Forum Discussion
How to count unique strings across multiple columns
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 tmpTableYou want Data and Table2 tables in the PBIX.
Once again - thank you!
this has definitely taken me closer, but its still listing out each work multiple times if it appears in multiple columns. For example, if 'shoe' appears in 'column 1' 20 times and 'column 4' 5 times, its listing out 'shoe' in the new column twice - once with a distinct count of 20 and the other showing 5.
I feel the last thing needed here is to merge them all together, but i dont think this is a simple case of removing dupes
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 tmpTableYou want Data and Table2 tables in the PBIX.
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...
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!