Forum Discussion

Lednum861's avatar
Lednum861
New Member
9 years ago
Solved

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...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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 () )
        )
    )
)
bblais's avatar
bblais
Resolver III
9 years ago

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:

 

  • bblais's avatar
    bblais
    Resolver III
    9 years ago

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

     

  • Lednum861's avatar
    Lednum861
    New Member
    9 years ago

    Your solution worked perfectly. Thank you very much!