Forum Discussion

projman's avatar
projman
Frequent Visitor
5 years ago
Solved

How to count unique strings across multiple columns

Hi I am hoping someone can help me with a table i am struggling with.   I have multiple columns with varying numbers of rows in each.  Across the rows/columns are various single word strings, and ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    projman OK, I mocked this up. PBIX is attached under signature. I think I left out a DISTINCT in the table calc so that is there now.

    Table2 = 
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4])
VAR tmpCol5 = SELECTCOLUMNS(Data,"Column",[Column5])
VAR tmpTable = DISTINCT(UNION(tmpCol1,tmpCol2,tmpCol3,tmpCol4,tmpCol5))
RETURN tmpTable

    You want Data and Table2 tables in the PBIX.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

projman OK, I mocked this up. PBIX is attached under signature. I think I left out a DISTINCT in the table calc so that is there now.

Table2 = 
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4])
VAR tmpCol5 = SELECTCOLUMNS(Data,"Column",[Column5])
VAR tmpTable = DISTINCT(UNION(tmpCol1,tmpCol2,tmpCol3,tmpCol4,tmpCol5))
RETURN tmpTable

You want Data and Table2 tables in the PBIX.

projman's avatar
projman
Frequent Visitor
5 years ago

Thank you!  Thats the column finally sorted 🙂 thank you so much

For some reason, my measure however is just showing '1' for every value.  Im not sure why this is as im using the same code as you are, but with my variables in there. I shall keep looking into it...

  • projman's avatar
    projman
    Frequent Visitor
    5 years ago

    Sorted it!  I had an extra distint in my MC Count measure that basically meant it was only ever looking at 1 string! All working now, thank you so much for your help!