Count distinct text in different columns

[Lednum861]

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!!

[bblais]

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:

[Lednum861]

Your solution worked perfectly. Thank you very much!

[bblais]

And I also fully support @Vvelarde's solution as well if you want your visuals to look more like this:

[Vvelarde · Accepted solution]

@Lednum861

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 () )
        )
    )
)

[Lednum861]

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!