Forum Discussion
Count distinct text in different columns
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!!
5 Replies
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!
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:
