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 am new to PowerBI and trying to create a report along with a slicer. When I import my tables in Power BI, I see it recognizes relation properly. But when I add slicer it does not work.
My Tables are as below...
Table:1 > Region
id | Name |
1 | APAC |
2 | EMEA |
Table:2 > Territory
id | name |
1 | Japan |
2 | India |
3 | Sri Lanka |
4 | Japan |
5 | London |
6 | Paris |
7 | Italy |
Table:3 > Company
id | name |
1 | company-1 (Note: part of Apac region with India & Sri Lanka territory) |
2 | company-2 (Note: part of Emea region with Paris & Italy territory) |
Table:4 > region_territory
id | region_id | territory_id |
1 | 1 | 1 |
2 | 1 | 2 |
3 | 1 | 3 |
4 | 1 | 4 |
5 | 2 | 5 |
6 | 2 | 6 |
7 | 2 | 7 |
Table:5 > company_region_territory
id | company_id | region_territory_id |
1 | 1 | 2 |
2 | 1 | 3 |
3 | 2 | 6 |
4 | 2 | 7 |
I am trying to create a slicer by "Region" name, so when I select Region as "APAC" it should show me total count of "Company" which has Region APAC assigned. When I connect PowerBI to my DB and select all above tables I see power bi has identified relationship properly, but still slicer does not work as expected.
Please share your valuable feedback to resolve this issue.
Thank you.
Solved! Go to Solution.
Hi @alfsender ,
Create a measure as below:
Measure =
var _id=CALCULATE(MAX('Region'[id]),FILTER(ALL(Region),'Region'[Name]=SELECTEDVALUE(Region[Name])))
var _territory=CALCULATETABLE(VALUES('region_territory'[territory_id]),FILTER(ALL(region_territory),'region_territory'[region_id]=_id))
var _companyid=CALCULATETABLE(VALUES('company_region_territory'[company_id]),FILTER(ALL(company_region_territory),'company_region_territory'[region_territory_id] in _territory))
Return
COUNTX(_companyid,[company_id])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @alfsender ,
Create a measure as below:
Measure =
var _id=CALCULATE(MAX('Region'[id]),FILTER(ALL(Region),'Region'[Name]=SELECTEDVALUE(Region[Name])))
var _territory=CALCULATETABLE(VALUES('region_territory'[territory_id]),FILTER(ALL(region_territory),'region_territory'[region_id]=_id))
var _companyid=CALCULATETABLE(VALUES('company_region_territory'[company_id]),FILTER(ALL(company_region_territory),'company_region_territory'[region_territory_id] in _territory))
Return
COUNTX(_companyid,[company_id])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
116 | |
104 | |
77 | |
71 | |
51 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |