Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, i have two tables looking like this:
Locations:
ID | Location Name |
1 | Location 1 |
2 | Location 2 |
3 | Location 3 |
Requests:
ID | name | Location 1 | Location 2 |
1 | Jon | 1 | 3 |
2 | Mike | 2 | 1 |
3 | Bob | 1 | 2 |
4 | Bill | 3 |
Now I want to display a Filter in the Report, where the users can select a location. After selection, the Request table should be filtered and show only data where the selected location is in column "location 1" or in column "location 2".
Any ideas how to do this, because I can only create one active relationship? i struggle with this. thanks for your help!
Solved! Go to Solution.
@stauffermi , do not join two tables or create one more independent table
Then try measure like
measure =
var _tab = summarize(allselected(Table1), Table1[Location ID])
return
Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab || Table[Location 2] in _tab ) )
or
measure =
var _tab = summarize(allselected(Table1), Table1[Location ID])
return
Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab || Table[Location 2] in _tab ), values(Table2[ID]) )
Thank you very much for your help! I was able to solve it with the measure.
First you need to create duplicate request table and then create join table as per below
then use single filter for both the table as per below
@stauffermi , do not join two tables or create one more independent table
Then try measure like
measure =
var _tab = summarize(allselected(Table1), Table1[Location ID])
return
Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab || Table[Location 2] in _tab ) )
or
measure =
var _tab = summarize(allselected(Table1), Table1[Location ID])
return
Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab || Table[Location 2] in _tab ), values(Table2[ID]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |