A INNER JOIN .. WHERE with >= translated from SQL to DAX maybe FILTER()

M

marious

Board Regular
Joined
Mar 3, 2013
Messages
226
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
I need a DAX formula ( calculated field) maybe FILTER () or CALCULATE () (not a expert in DAX that assign the correct High school (table: tabSBISD) to the correct address (table consolidatedOne).
I have 3 tables.
1) tabSBISD assigns schools from - to address. This able contains all the schools ,but assign them (from - to)
2) This table contains the street numbers and an index so I can crate relationships between 3 tables.
3 Contains the Address. Ineed to lookup the correct High school for each address.
This s a simple quer in SQL:
[SELECT ConsolidateOne.StreetNum, ConsolidateOne.StreetName, tabSBISD.[High School]
FROM (tabStreets INNER JOIN ConsoliatedOne ON tabStreet.StreetName=ConsolidatedOne.treetName) INNER JOIN tabSBISD ON tabStreets.StreetNam=abSBISD.Streetnme
WHERE (((tabSBISD.FromNumbe)<=[StreetNum]) AND abSBIS.ToNumber)>=[streetNum]))]

Thank you very much for your time
Marious




<!-- ######### Start Created Html Code To Copy ########## -->
DAX

*
A
B
C
D
E
F
G
H
I
J
K
L
M
N
tabSBISD
*
*
*
*
tabStreet
*
*
ConsolidatedOne
*
*
*
*
*
StreetName
FromNumber
ToNumber
High School
*
ID
StreetName
*
StreetName
StreetNum
Calculated_Field
*
*
Correct Answer
First
SchoolName1
*
First
*
First
*
*
SchoolName1
First
SchoolName2
*
Second
*
First
*
*
SchoolName1
First
SchoolName3
*
Third
*
First
*
*
SchoolName2
Third
SchoolName2
*
Main
*
First
*
*
SchoolName3
Third
SchoolName3
*
*
*
*
Main
*
*
SchoolName3
Third
N/A
*
*
*
*
Main
*
*
SchoolName1
Third
SchoolName
*
*
*
*
Main
*
*
SchoolName3
Main
SchoolName3
*
*
*
*
Third
*
*
*
Main
SchoolName1
*
*
*
*
Third
*
*
SchoolName3

<TBODY>
[TD="bgcolor: #cacaca, align: center"]4
[/TD]

[TD="bgcolor: #cacaca, align: center"]5
[/TD]

[TD="bgcolor: #cacaca, align: center"]6
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]100
[/TD]

[TD="align: right"]1
[/TD]

[TD="align: right"]50
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]7
[/TD]

[TD="align: right"]100
[/TD]
[TD="align: right"]300
[/TD]

[TD="align: right"]2
[/TD]

[TD="align: right"]55
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]8
[/TD]

[TD="align: right"]300
[/TD]
[TD="align: right"]3000
[/TD]

[TD="align: right"]3
[/TD]

[TD="align: right"]150
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]9
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]500
[/TD]

[TD="align: right"]4
[/TD]

[TD="align: right"]325
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]10
[/TD]

[TD="align: right"]500
[/TD]
[TD="align: right"]1000
[/TD]

[TD="align: right"]2
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]11
[/TD]

[TD="align: right"]1000
[/TD]
[TD="align: right"]2000
[/TD]

[TD="align: right"]600
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]12
[/TD]

[TD="align: right"]2000
[/TD]
[TD="align: right"]3000
[/TD]

[TD="align: right"]3
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]13
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]500
[/TD]

[TD="align: right"]1000
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

[TD="bgcolor: #cacaca, align: center"]14
[/TD]

[TD="align: right"]500
[/TD]
[TD="align: right"]1000
[/TD]

[TD="align: right"]550
[/TD]

[TD="bgcolor: #ffff00"]*
[/TD]

</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
　
　
<!-- ######### End Created Html Code To Copy ########## -->
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
There are errrors in the previous excel data sample, please use this one. <html><head><title>Excel Jeanie HTML</title></head><body>
<!-- ######### Start Created Html Code To Copy ########## -->
Excel Workbook
ABCDEFGHIJKLMN
3Table1Table2Table3
4tabSBISDtabStreetsConsolidatedOne
5StreetNameFromNumberToNumberHigh SchoolIDStreetNameStreetNumStreetNameCalculated_FieldCorrect Answer
6First1100SchoolName11First50FirstSchoolName1
7First100300SchoolName22Second55FirstSchoolName1
8First3003000SchoolName33Third150FirstSchoolName2
9Third1500SchoolName24Main325FirstSchoolName3
10Third5001000SchoolName32MainSchoolName3
11Third10002000N/A600MainSchoolName1
12Third20003000SchoolName3MainSchoolName3
13Main1500SchoolName31000Third
14Main5001000SchoolName1550ThirdSchoolName3
DAX

?
?
<!-- ######### End Created Html Code To Copy ########## -->
</body></html>
 
Upvote 0
This is the solution using an array formula but I still want to know the solution with DAX to compare. So far MS Access 20 min, Excel array formula 30 min but I don't know the DAX answer.
Everything helps.
Regards,
Mario

<!-- ######### Start Created Html Code To Copy ########## -->
DAX

*ABCDEFGHIJKLM
Array formula to solve INNER JOIN************
*************
*************
tabSBISD****tabStreet**ConsolidatedOne****
***
FirstSchoolName1*First*First*SchoolName1
FirstSchoolName2*Second*First*SchoolName1
FirstSchoolName3*Third*First*SchoolName2
ThirdSchoolName2*Main*First*SchoolName3
ThirdSchoolName3****Main*SchoolName3
ThirdN/A****Main*SchoolName1
ThirdSchoolName****Main*SchoolName3
MainSchoolName3****Third**
MainSchoolName1****Third*SchoolName3

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"><COL style="WIDTH: 53px"><COL style="WIDTH: 84px"><COL style="WIDTH: 84px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 63px"><COL style="WIDTH: 110px"><COL style="WIDTH: 102px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ccffff"]StreetName[/TD]
[TD="bgcolor: #ccffff"]FromNumber[/TD]
[TD="bgcolor: #ccffff"]ToNumber[/TD]
[TD="bgcolor: #ccffff"]High School[/TD]

[TD="bgcolor: #ccffff"]ID[/TD]
[TD="bgcolor: #ccffff"]StreetName[/TD]

[TD="bgcolor: #ccffff"]StreetName[/TD]
[TD="bgcolor: #ccffff"]StreetNum[/TD]
[TD="bgcolor: #ccffff"]Calculated_Field[/TD]

[TD="bgcolor: #ccffff"]Correct Answer[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]50[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]300[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]55[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]150[/TD]

[TD="bgcolor: #ffff00"]SchoolName2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]325[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]600[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]1000[/TD]

[TD="bgcolor: #ffff00"]N/A[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]550[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

</TBODY>

Spreadsheet Formulas
CellFormula
K6{=OFFSET($A$6,LARGE((($A$6:$A$14)=J6)*(($B$6:$B$14)<=I6)*(($C$6:$C$14)>=I6)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K7{=OFFSET($A$6,LARGE((($A$6:$A$14)=J7)*(($B$6:$B$14)<=I7)*(($C$6:$C$14)>=I7)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K8{=OFFSET($A$6,LARGE((($A$6:$A$14)=J8)*(($B$6:$B$14)<=I8)*(($C$6:$C$14)>=I8)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K9{=OFFSET($A$6,LARGE((($A$6:$A$14)=J9)*(($B$6:$B$14)<=I9)*(($C$6:$C$14)>=I9)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K10{=OFFSET($A$6,LARGE((($A$6:$A$14)=J10)*(($B$6:$B$14)<=I10)*(($C$6:$C$14)>=I10)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K11{=OFFSET($A$6,LARGE((($A$6:$A$14)=J11)*(($B$6:$B$14)<=I11)*(($C$6:$C$14)>=I11)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K12{=OFFSET($A$6,LARGE((($A$6:$A$14)=J12)*(($B$6:$B$14)<=I12)*(($C$6:$C$14)>=I12)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K13{=OFFSET($A$6,LARGE((($A$6:$A$14)=J13)*(($B$6:$B$14)<=I13)*(($C$6:$C$14)>=I13)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}
K14{=OFFSET($A$6,LARGE((($A$6:$A$14)=J14)*(($B$6:$B$14)<=I14)*(($C$6:$C$14)>=I14)*(ROW($C$6:$C$14)-ROW($C$6)),1),3)}

<TBODY>
</TBODY>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
　
　
<!-- ######### End Created Html Code To Copy ########## -->

 
Last edited:
Upvote 0
The previous data sample wasn't correct
1) I changed the array formula so in case that there is no school assigned "No Offset" the formula returns No High school
2) Block numbers can't overlap ranges should be 1 to 100, 101 to 300 etc.

<!-- ######### Start Created Html Code To Copy ########## -->
DAX

*ABCDEFGHIJKLMN
tabSBISD****tabStreet**ConsolidatedOne*****
****
FirstSchoolName1*First*First*SchoolName1*
FirstSchoolName2*Second*First*SchoolName1*
FirstSchoolName3*Third*First*SchoolName2*
ThirdSchoolName2*Main*First*SchoolName3*
ThirdSchoolName3****Main*SchoolName3*
ThirdN/A****Main*SchoolName1*
ThirdSchoolName2****Main*SchoolName3*
MainSchoolName3****Third***
MainSchoolName1****Third*SchoolName3*
**************

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 65px"><COL style="WIDTH: 53px"><COL style="WIDTH: 84px"><COL style="WIDTH: 96px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 63px"><COL style="WIDTH: 110px"><COL style="WIDTH: 102px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ccffff"]StreetName[/TD]
[TD="bgcolor: #ccffff"]FromNumber[/TD]
[TD="bgcolor: #ccffff"]ToNumber[/TD]
[TD="bgcolor: #ccffff"]No High School[/TD]

[TD="bgcolor: #ccffff"]ID[/TD]
[TD="bgcolor: #ccffff"]StreetName[/TD]

[TD="bgcolor: #ccffff"]StreetName[/TD]
[TD="bgcolor: #ccffff"]StreetNum[/TD]
[TD="bgcolor: #ccffff"]Calculated_Field[/TD]

[TD="bgcolor: #ccffff"]Correct Answer[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]300[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]55[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]301[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]150[/TD]

[TD="bgcolor: #ffff00"]SchoolName2[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]325[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]1001[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]600[/TD]

[TD="bgcolor: #ffff00"]SchoolName1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]2001[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]1000[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]501[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]550[/TD]

[TD="bgcolor: #ffff00"]SchoolName3[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</TBODY>

Spreadsheet Formulas
CellFormula
K6{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J6)*(($B$6:$B$14)<=I6)*(($C$6:$C$14)>=I6)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K7{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J7)*(($B$6:$B$14)<=I7)*(($C$6:$C$14)>=I7)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K8{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J8)*(($B$6:$B$14)<=I8)*(($C$6:$C$14)>=I8)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K9{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J9)*(($B$6:$B$14)<=I9)*(($C$6:$C$14)>=I9)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K10{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J10)*(($B$6:$B$14)<=I10)*(($C$6:$C$14)>=I10)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K11{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J11)*(($B$6:$B$14)<=I11)*(($C$6:$C$14)>=I11)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K12{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J12)*(($B$6:$B$14)<=I12)*(($C$6:$C$14)>=I12)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K13{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J13)*(($B$6:$B$14)<=I13)*(($C$6:$C$14)>=I13)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}
K14{=OFFSET($A$5,LARGE(--(($A$6:$A$14)=J14)*(($B$6:$B$14)<=I14)*(($C$6:$C$14)>=I14)*(ROW($C$6:$C$14)-ROW($C$5)),1),3)}

<TBODY>
</TBODY>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie HTML 4
　
　
<!-- ######### End Created Html Code To Copy ########## -->

 
Upvote 0
I finally found the solution. The title for my post should be " How t relate tables in DAX without using relationships". I want to thanks Mr. Marco Russo for taking the time to post this solution in his blog. SQLBI - Marco Russo : How to relate tables in DAX without using relationships
Since I need to filter based in two fields I adapted this formula
['Orders'[ShipmentCost] = SUMX( 'Orders', 'Orders' [Weight] *
CALCULATE( VALUES( 'PriceList'[Cost] ),
FILTER( 'PriceList',
'PriceList'[Country] = 'Orders'[Country]
&&
'PriceList'[Zone] = 'Orders'[Zone] ) ) )]
I also found useful this info related to double pipe operators for the AND = && OR =|| syntax
I still have to practice with the use of () and still think that a solution using USERELATIONSHIP() is viable.
DAX Operator Reference for Power Pivot (DAX) - Excel - Office.com
 
Upvote 0
=CALCULATE(VALUES(tabSBISD[No High School] ),FILTER(tabSBISD, tabSBISD[StreetName]=ConsolidatedOne[StreetName] && tabSBISD[FromNumber]<=ConsolidatedOne[StreetNumber] && tabSBISD[ToNumber]>=ConsolidatedOne[StreetNumber]))

It works
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,986
Messages
6,175,789
Members
452,670
Latest member
nogarth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top