Finding totals from multiple columns

[Carter364]

Hi there, I am looking to find common themes across multiple columns. I have changed my data as an example as the data I am using is personal. But in this example I have multiple flags and multiple colums of the colours in the flags.

Is there a way I can use an "if" function where if contains 'red' across any of the colour columns then 'Yes', if not then 'No' ?

Any help would be much appreciated.

[ryan_mayu]

do you mean if there is a red in the colour 1 column then Yes? 

what's the expected output based on the sample data you provided?

[Carter364]

It's hard to explain because the actual data I am using is quite specific. But ultimatley It would say if red is in colour 1, colour 2, colour 3, or colour 4 then "Yes"

I would do this for various colours, then I would be able to determine how many flags contain red, how many flags contain blue etc.

The data I am using contains 3000+ rows of data

[ryan_mayu · Accepted solution]

you can unpivot tables in the PQ

then create a dim table that you can filter

create a measure

Measure = DISTINCTCOUNT('Table'[Flag])

pls see the attachment below