Search code examples
countpowerbidaxpowerbi-desktopdistinct-values

Power BI / DAX - COUNT Frequency of Unique strings/values across multiple columns

I have a table with 3 columns like this:

Table1

enter image description here

In PowerBI I would like to count the frequency of each unique value/string and get it in a seperate table like this:

Table2

enter image description here

I'm finding all different ways of getting a list of the unique values like this:

Table2 = 
     DISTINCT(
          UNION(
              DISTINCT('Table1'[Column1]),
              DISTINCT('Table1'[Column2]),
              DISTINCT('Table1'[Column3])
          )
     )

Which now only gives me a list of distinct values/strings, but not with the count:

enter image description here

And I don't know how to get the count column with it.

Could someone help me to get the counts with it as well?

Solution

  • Using calculated DAX table can lower your performances, depending on the size of your dataset. The easiest way would have been to do the transformation in Power Query: new query based on source table, delete unrelevant columns, unpivot your 3 columns, then you can summarize and count on a visual matrix/table.