Dynamic Calculation Group for Data Quality Validation Based on Selected Columns

[sonnyvb]

Hello,

I would like to create a Calculation Group in my Power BI model that allows me to dynamically apply a specific measure based on the selected column. Specifically, I want to implement a data quality rule that checks whether a column contains blank values, total valueas, .

The idea is as follows:

• The Calculation Group should include a measure that checks if a selected column contains blank values.
• I want to repeat this logic for multiple columns in the same table, where each Calculation Item in the group corresponds to a specific column.

My goal is to make this dynamic and reusable so that users can choose which column they want to validate in the report environment.

For example:
DQM CountryOfOrigin =
VAR TotalRows = COUNTROWS(view_BI_M_Part)
VAR TotalEmptyRows = COUNTROWS(
FILTER(
view_BI_M_Part,
ISBLANK(view_BI_M_Part[CountryOfOrigin]) ||
TRIM(view_BI_M_Part[CountryOfOrigin]) = "" // Checks for empty strings or spaces
)
)
VAR TotalFilledRows = TotalRows - TotalEmptyRows
VAR PercentageEmptyRows = DIVIDE(TotalEmptyRows, TotalRows, 0)
VAR PercentageFilledRows = DIVIDE(TotalFilledRows, TotalRows, 0)

Is this possible? If yes, how can I implement this using Tabular Editor 3 and Power BI?

[mlonsk]

Hi @sonnyvb

I must admit that I am a bit bewildered about how you describe your requirements.
On the one hand, you want to use the calculation group on the selected column (a term I do not think exists in calculation groups), which is dynamic and reusable. On the other hand, you wish to create a calculation item for specific columns, which, to my understanding, contradicts the first part.

Your example includes five different calculations; however, a measure or calculation item can only return one scalar value. Therefore, you need to choose one of those variables to return or create five separate measures to achieve what you need.
One way to do that is to create a C# script that, for each of the columns you want to check, creates the five measures with that column. You can use this script as inspiration: https://docs.tabulareditor.com/common/CSharpScripts/Beginner/script-create-sum-measures-from-columns.html

Another option is to use Power Query to create Data Profiling tables and to have in your report environment shown by Reza Rad:
https://radacad.com/create-a-profiling-report-in-power-bi-give-the-end-user-information-about-the-data