Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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!
User | Count |
---|---|
64 | |
59 | |
46 | |
31 | |
31 |
User | Count |
---|---|
84 | |
73 | |
52 | |
50 | |
44 |