Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I'm very new to BI. I have my database setup similar to the table below. I want to use 2 tools in BI. One is a slicer and the other is a visual that counts and provides a total. I know how to use both tools but I can't get the count visual to return the corrent value.
Consider the data below...I want to put the 'part #' in the slicer. If I click on part # 111 in the slicer, I want the counter to return a value of 2 (2 distinct countries). If I click on 222 in the slicer, I want the counter to return a value of 1 (1 distinct country). Same for part # 333 (1 distinct country). 444 would return a value of 2 and 555 a value of 1. Is this possible? Thank you very much for your time and help!!
Solved! Go to Solution.
Hi, try using this Dax:
Measure = COUNTROWS ( DISTINCT ( UNION ( FILTER ( VALUES ( Table1[MFR1 Country] ), Table1[MFR1 Country] <> BLANK () ), FILTER ( VALUES ( Table1[MFR2 Country] ), Table1[MFR2 Country] <> BLANK () ) ) ) )
I would recommend that you pivot your data in the Edit Queries screen, something like this:
The table starts out looking like this:
Select the Part # column ONLY, and then choose Unpivot Other Columns in the Transform Ribbon:
Then it will look like this:
Then rename your columns to Manufacturer and Country and close and apply. Now you can add a measure like this:
Country Count = DISTINCTCOUNT(Manufacturers[Country])
And use this measure in a table or other visual like this:
Your solution worked perfectly. Thank you very much!
And I also fully support @Vvelarde's solution as well if you want your visuals to look more like this:
Hi, try using this Dax:
Measure = COUNTROWS ( DISTINCT ( UNION ( FILTER ( VALUES ( Table1[MFR1 Country] ), Table1[MFR1 Country] <> BLANK () ), FILTER ( VALUES ( Table1[MFR2 Country] ), Table1[MFR2 Country] <> BLANK () ) ) ) )
Yes, this worked. Thank you so much. I actually tried your solution and the solution in the other post I received and both work perfectly. Thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
86 | |
79 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |