Measure to Dynamically Iterate Through List of Columns

[mmoggia]

Hi,

I have a list of columns on which I need to perform some data quality controls (i.e., attributes are blank).

Is there a way to create a DAX measure to dynamically iterate through a list of columns without having to specify each 'Table'[ColumnName]?

Thanks,

Massimiliano

[v-yohua-msft · Accepted solution]

Hi, @mmoggia 

It's not practical to use DAX expressions for large-scale data quality control and not specify column names.

Perhaps you can reverse many columns into one column in Power Query, and then perform data quality control in another column, following the Links：

Unpivot columns (Power Query) - Microsoft Support

Instead of trying to implement it in DAX, you can also use an external tool like Python to handle it in Power Query.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[mmoggia]

It's actually a very good idea to unpivot all the columns keeping only the table PK and create a 1:* relationship (original table --> unpivoted table: PK + DQColumnName + DQColumnValue).

[123abc]

In Power BI, creating a dynamic measure to iterate through a list of columns without explicitly specifying each column name can be achieved using DAX and some advanced techniques like iterating functions and dynamic expressions.

Here's a general approach you can follow:

1. Create a Parameter Table: Start by creating a table in your Power BI model that lists all the columns you want to include in your data quality controls. Let's call this table ColumnList and assume it has a column named ColumnName which contains the names of the columns you want to iterate through.

2. DAX Measure: Create a DAX measure that dynamically iterates through the list of columns and performs your data quality control checks. You can use DAX functions like SELECTCOLUMNS, SUMX, and ISBLANK for this purpose.

Here's a simplified example of how your DAX measure might look:

DynamicDataQualityCheck =
VAR SelectedColumns = VALUES(ColumnList[ColumnName])
RETURN
SUMX(
SelectedColumns,
IF(
ISBLANK([ColumnName]), // You need to replace [ColumnName] with the actual column reference
1, // Return 1 if the condition is met, you can modify this according to your specific data quality check
0
)
)

In this measure:

• VALUES(ColumnList[ColumnName]) fetches the distinct column names from your parameter table.
• SUMX iterates over each column in SelectedColumns.
• IF(ISBLANK([ColumnName]), 1, 0) checks if the current column is blank, then returns 1 if it's true, and 0 otherwise. You need to replace [ColumnName] with the actual column reference in your dataset.

1. Visualization: Once you've created the measure, you can use it in your visualizations to monitor the data quality across the selected columns dynamically.

Remember to adjust the measure according to your specific data quality requirements and column structures in your dataset. This approach allows you to add or remove columns from your data quality checks without modifying the measure itself, providing the desired dynamic behavior.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[mmoggia]

Hi, thanks a lot for providing your input.

I cannot actually make it work.

Let's say I have 100 columns ('Table'[column1],..., 'Table'[column100]), and I need to flag (0/1) all those rows in the table that have at least 1/100 fields blank. How can achieve this without hardcoding all the 100 checks with IFs in DAX measure?

[123abc]

To achieve your goal of flagging rows in the table that have at least one blank field among 100 columns without hardcoding each check, you can utilize DAX functions dynamically to iterate through all the columns. One efficient approach is to use the SELECTCOLUMNS function combined with the UNION function to create a table containing all the columns you want to check for blanks. Then, you can use the HASONEVALUE function to determine if any of the columns in a row are blank.

Here's how you can implement it:

BlankFlag =
IF(
HASONEVALUE (
UNION (
SELECTCOLUMNS('Table', "Column", 'Table'[Column1]),
SELECTCOLUMNS('Table', "Column", 'Table'[Column2]),
... Repeat for all 100 columns
SELECTCOLUMNS('Table', "Column", 'Table'[Column100])
)
),
0,
1
)

In this measure:

• SELECTCOLUMNS is used to create a virtual table with a single column each for all the 100 columns you want to check.
• UNION combines all these columns into a single table.
• HASONEVALUE checks if there's only one distinct value in the combined table for each row. If there's only one distinct value, it means all the columns were blank in that row.
• If HASONEVALUE returns true, it means at least one column was blank, so the measure returns 1, otherwise, it returns 0.

This approach dynamically generates the list of columns without the need to hardcode each column individually. However, keep in mind that creating such a measure might have performance implications, especially with a large dataset. Therefore, it's advisable to test and optimize the performance as needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[mmoggia]

Is there a way to avoid having to specify each column name in the dax measure? I have more than 100 columns and it would be much better to have all the columns listed in a separate table (or in a list), and then iterate through each column.

[igorplotnikov]

@mmoggia 
I believe @123abc just inserts here what he's got from ChatGPT, those answers don't make much sense