Disctinct count of different values

[IF]

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?

No	Type	C1	C2	C3	C4	C5	C6	Value
1	A11	343654666	4	4467	5454	34	670	10
2	A12	343654666	4	4467	5454	34	670	11
3	A13	343654666	#	4467	767675	34	670	15
4	A14	343654666	4	32242	5454	34	670	5
5	A15	343533455	2	4467	5454	34	655	3
6	A16	7675645454	4	4467	5454	34	670	2
7	A17	343654666	4	44322	5454	34	670	20
8	A18	343654666	4	44322	5454	34	670	30
9	A19	343654666	#	4467	5454	55	443	15
10	A20	343654666	4	4467	65432	45	443	25

Kind regards

[mahoneypat · Accepted solution]

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