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.
I currently have two tables in this dataset.
Table 1: Table 2:
Company Name Location Company Name Location Type
------------------ ----------------- ------------------ ------------------
Breeze Chicago, Illinois Breeze Office
Alter Denver, Colorado Breeze Factory
Alter Office
It is a one-many relationship and I want to be able to filter all of our locations based off the location type. Currently if I select "Office" it shows Breeze and Alter locations. If I do multiselect and click "Office" and "Factory" it still shows both Breeze and Alter. I would only want it to show Breeze since this company contains both location types. Any ideas?
Hi, @wainnoce
According to your description, you want to make the table only shows “Breeze” when you select both location types, you can try my steps:
Measure =
var _1=CALCULATE(MAX([Company Name]),FILTER(all('Table 2'),[Location Type]="Office"))
return
IF("Office" in SELECTCOLUMNS('Table 2',"1",[Location Type])&&
COUNTX(ALLSELECTED('Table 2'),[Location Type])>2,
IF(MAX('Table 1'[Company Name ])=_1,1,0)
,1)
And you can get what you want, like this:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
With this example though if I select no filters it is only showing Breeze. I would like to be able to display all possible locations on a map so this would never show all results to my users. Not to mention when a location ends up having 5 different location types "Office", "Factory", "Warehouse", etc. not sure how your measure would hold up.
Hi @wainnoce
Whatever you select in one field would filter what is against it in the other field, based on your thing, I think you can make a concatenated column say ex office-factory , office, factory in one column and whenever you select anything from this concatenated column the results would pop out based on your expectation.
Please mark this as solution if you think this can solve your problem buddy.
I get what you are going for but I dont think this would work. If I was to select "Office" in a filter then it would display all office locations. If I selected both "Office" and "Factory" it should show all locations that have an "Office" and "Factory" on-site. With your proposed filter idea there would be now way to anticipate what combination I am selecting once 5+ location types get thrown into the mix.
The issue with this is we have up to 10 different location types and some locations have up to 5 types. Would make the filter not user friendly.
What about giving a same slicer and dragging one field over the other. Have you tried thay?
Not sure I get what you mean.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |