how to create a measure which checks if selected value contains in another table & filters third tab

[Anonymous]

Hello,

Please help me in creating a measure to achieve the below ask.(sample pbix attached)

sample pbix 

I have 3 tables namely

Main

Detailed

City

Main and Detailed are related based on column "id" however City is not related to any other table.

what I'm trying to achieve is if I select a city or multiple cities from the slicer created from the City[Cities] example lets say I select "san fransisco" ,"washington" and "france" I want the Main table to be filtered of id's if the city contains in start or end city. If you look the below examples since id = d001 does not contain any of the selected city its excluded similarly if id a001/c001/b001 contains at least one city from the selected list so it should show the entrie entries instead of just the entries which are selected. Please advise on how to achieve this?

 Below is a sample example i've shown

sample matrix visual BEFORE SELECTION

sample matrix visual AFTER SELECTION

 I hope i've explained this properly please let me know if you have any questions.

Kindly help thank you

[amitchandak]

@Anonymous , Trt like

calculate(count(Detailed[Id]), filter(Detailed, Detailed[Start City] in values(City[Cities])))
or
calculate(count(Detailed[Id]), filter(Detailed, Detailed[End City] in values(City[Cities])))

or
calculate(count(Detailed[Id]), filter(Detailed, Detailed[Start City] in values(City[Cities]) || Detailed[End City] in values(City[Cities])))

or
calculate(count(Detailed[Id]), filter(Detailed, Detailed[Start City] in allselected(City[Cities]) || Detailed[End City] in allselected(City[Cities])))

[Anonymous]

@amitchandak 

Those measure which you mentioned filters out Detailed table whereas I want the main table to be filtered based on the filter applied on detailed table so the complete details of an ID show up even if a single city is in the list.

example o/p when i use the meaure as : calculate(count(Detailed[Id]), filter(Detailed, Detailed[Start City] in allselected(City[Cities]) || Detailed[End City] in allselected(City[Cities])))

expected ouput for same selection

[tex628 · Accepted solution]

This was a bit harder than expected 🙂

Take a look at the attached file and the "Filter" measure. 

[Anonymous]

@tex628 
WOW you are genius 👏

Your solution works great. I've breaking my head for weeks to figure this out honestly i had no idea you can save a table inside a variable and access its columns.... Thank you very much I learnt quite a few tricks with your solution.

One last favour could you please point me to a good source where I can learn the basics to advance level or DAX? Course on SQLBI is pretty expensive are there any free course where I can learn from basics to advance? I've been strguggling to understand how the computation happens(row wize or colum wize etc) 

[tex628]

Honestly speaking I don't know of any structured training that is either free or not terribly expensive. Most free material that can be found is generally aimed at either specific issues/syntax or showcase how to do "cool" workarounds like creating bookmark filter panes and similar. Most courses are generally very basic. 

I'm really hoping that something comes along that is similar to W3Schools and allows you to experience and actively practice advanced DAX / Power Query. 

Br,
J

[Anonymous]

@tex628 completely agree something like w3schools would be so cool.