Forum Discussion

IF's avatar
IF
Post Prodigy
5 years ago
Solved

Disctinct count of different values

Hi,

I want to get distinctcount of the values from 6 different columns (C1,C2,C3,C4,C5, and C6). I exclude if the C2 is #. What I do is that I copy those and merge them in one column, named "All".

Measure = CALCULATE(DISTINCTCOUNT('Table'[All]), 'Table'[C2]<>"#")
It works and brings the result as 6 by counting No. 1, 2 and 6 as 1; and excluding no 3, and no 9.  I have quite a lot of data and this way is slowing down the process. When I merge them, they are about 23-26 characters, which doesn't even allow me to use the whole number format. Is there any other way of doing the same without exhausting the memory very much?
 
NoTypeC1C2C3C4C5C6Value
1A113436546664446754543467010
2A123436546664446754543467011
3A13343654666#44677676753467015
4A143436546664322425454346705
5A15343533455244675454346553
6A167675645454444675454346702
7A1734365466644432254543467020
8A1834365466644432254543467030
9A19343654666#446754545544315
10A2034365466644467654324544325

 

 
 
Kind regards
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You could try using a measure instead like this one (don't create the concatenated column)

     

    NewMeasure =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table, Table[C2] <> "#" ),
            Table[C1],
            Table[C2],
            Table[C3],
            Table[C4],
            Table[C5],
            Table[C6]
        )
    )

     

    Pat

     

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You could try using a measure instead like this one (don't create the concatenated column)

     

    NewMeasure =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table, Table[C2] <> "#" ),
            Table[C1],
            Table[C2],
            Table[C3],
            Table[C4],
            Table[C5],
            Table[C6]
        )
    )

     

    Pat