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'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!
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 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |