Dax - Check if one row has the same value for all Columns

[MrMatsson]

Hello,
I have the scenario where I have way to many columns to manually type them out in dax yet I want to create a messure to check if any AssetClass either only have the value "Yes" or only have the value "No" for all my columns. Like for example the AssetClass "Bonds" in the table below.

AssetClass	Column1	Column2	Column3	...	Column99
FX	Yes	Yes	No		Yes
FI	No	No	Yes		No
Bonds	Yes	Yes	Yes		Yes

Started creating the code below which would count all the rows where all the columns are "yes" and than compare that to the total amount of rows to see if they match. But I'm not about to manually write out 100 different column names.

Anyone who knows a good way to check if all columns have the same value on the same row without having to manally type out every single coumlmn name?

AllYes2 =     
VAR _countYes =
    CALCULATE (
        COUNTA( 'Table1'[AssetClass]),
        FILTER ( ALL ( 'Table1' ), 
        'Table1'[Column1] = "YES" || 'Table1'[Column2] = "Yes")
    )
    VAR _countAll =
    CALCULATE ( COUNTA( 'Table1'[AssetClass] ) )
VAR result =
    SWITCH(TRUE(),
    _countYes = _countAll,"","")

    Return
    result

[v-tsaipranay · Accepted solution]

Hi @MrMatsson 

Thank you for reaching out to the Microsoft Fabric Community. Also thank you @sjoerdvn and @bhanu_gautam  for your inputs.

To address your requirement of checking if all columns for an AssetClass have either "Yes" or "No" values without manually listing all column names, here are two approaches:

1. Unpivot your table in Power Query so that all your columns (e.g., Column1, Column2, ...) become rows. This simplifies checking the values across columns. After unpivoting, you can easily create a DAX measure to check if all values are "Yes" or "No" for each AssetClass.
2. If unpivoting is not feasible, you can create a helper table with the column names and use DAX to iterate over these columns. For example:

Create a helper table containing column names:

ColumnsList = DATATABLE("ColumnName", STRING, {("Column1"), ("Column2"), ..., ("Column99")})

Use a DAX measure to check if all values in the columns are "Yes" or "No" for each AssetClass:

AllYesOrNo = 
VAR ColumnsCheck = 
    ADDCOLUMNS(ColumnsList, "ColumnValue", SWITCH(TRUE(), 'Table1'[ColumnName] = "Column1", 'Table1'[Column1], ...))
VAR CheckYes = COUNTROWS(FILTER(ColumnsCheck, [ColumnValue] = "Yes")) = COUNTROWS(ColumnsCheck)
VAR CheckNo = COUNTROWS(FILTER(ColumnsCheck, [ColumnValue] = "No")) = COUNTROWS(ColumnsCheck)
RETURN IF(CheckYes || CheckNo, "All Yes or All No", "Mixed")

• Unpivoting is the most efficient and dynamic solution. If you need to avoid unpivoting, use a helper table with DAX.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[sjoerdvn]

Hi, probably not the answer you are looking for, but you should realy avoid tables like this in power bi. Power BI is pretty good at handling lots of rows, but 99+ column tables, not such much. 
Try transposing your table and probably add a dimension for the transposed column names to. 

[MrMatsson]

Thanks for the reply Sjoerdvn.
Had the same idea but sadly I have around 50 rows as well. Sigh, looks like I'll just have to admit defeat and type out all the columns one by one 🙂

[bhanu_gautam]

@MrMatsson , Try using

dax
AllYesOrNo =
VAR ColumnsList =
UNION(
SELECTCOLUMNS(ALL('Table1'), "ColumnName", "Column1"),
SELECTCOLUMNS(ALL('Table1'), "ColumnName", "Column2"),
SELECTCOLUMNS(ALL('Table1'), "ColumnName", "Column3")
-- Add more columns as needed
)

VAR CheckYes =
COUNTROWS(
FILTER(
ColumnsList,
[ColumnName] = "Yes"
)
) = COUNTROWS(ColumnsList)

VAR CheckNo =
COUNTROWS(
FILTER(
ColumnsList,
[ColumnName] = "No"
)
) = COUNTROWS(ColumnsList)

RETURN
IF(CheckYes || CheckNo, "All Yes or All No", "Mixed")

[MrMatsson]

Thanks for the reply bhanu,
I'm sure your formula works better than mine but I'm looking for a way to not have to type out all my 99+ column names. Do you know a way to check the value of all columns without listing all column names one by one?