Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All,
I have a data set which I need to do some validation reporting on and so far have created calculated columns which test all rows in the data set for a Status , Acc No, and isVIP value, otherwise the value of 1 is returned in order to allow for counting all exceptions raised for each field per validation control.
Here is an example of what the source data will look like:
The rules that I have that each account has to abide by are:
I have managed to write a SAS script which can check each of these rows and to write either a 0 to represent a pass or 1 for an exception raised and show the total exceptions per validation rule. The output table required for reporting has to be in the following format:
Is it possible at all to produce the same result using either Power Query or DAX (or both :D) to produce this kind of table that can be used for visualizations?
Any guidance regarding what approach would be of much help! Hopefully others can learn something from this too.
Kind Regards,
Kyle
Solved! Go to Solution.
Hi @Kyle21 ,
Here are the steps you can follow:
1. Go to Power query and copy the Table.
2. Select 5 columns in Table2 -- Unpivot columns.
3. In the table formed by Unpivot columns, select [Value] – Remove.
4. Select [Attribute] – Remove Duplicates.
Result:
5. Create calculated column.
Code =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type","TY001",
'Table2'[Attribute]="Name","NA001",
'Table2'[Attribute]="Status","ST001",
'Table2'[Attribute]="isVIP","VIP001",
'Table2'[Attribute]="Account No","AN001")
Description =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type","Type is Required",
'Table2'[Attribute]="Name","Name is Required",
'Table2'[Attribute]="Status","Status is Required",
'Table2'[Attribute]="isVIP","A value is Required",
'Table2'[Attribute]="Account No","Account Number is Required")
Exceptions =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type",0,
'Table2'[Attribute]="Name",0,
'Table2'[Attribute]="Status",1,
'Table2'[Attribute]="isVIP",0,
'Table2'[Attribute]="Account No",1)
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Kyle21 ,
Here are the steps you can follow:
1. Go to Power query and copy the Table.
2. Select 5 columns in Table2 -- Unpivot columns.
3. In the table formed by Unpivot columns, select [Value] – Remove.
4. Select [Attribute] – Remove Duplicates.
Result:
5. Create calculated column.
Code =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type","TY001",
'Table2'[Attribute]="Name","NA001",
'Table2'[Attribute]="Status","ST001",
'Table2'[Attribute]="isVIP","VIP001",
'Table2'[Attribute]="Account No","AN001")
Description =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type","Type is Required",
'Table2'[Attribute]="Name","Name is Required",
'Table2'[Attribute]="Status","Status is Required",
'Table2'[Attribute]="isVIP","A value is Required",
'Table2'[Attribute]="Account No","Account Number is Required")
Exceptions =
SWITCH(
TRUE(),
'Table2'[Attribute]="Type",0,
'Table2'[Attribute]="Name",0,
'Table2'[Attribute]="Status",1,
'Table2'[Attribute]="isVIP",0,
'Table2'[Attribute]="Account No",1)
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Kyle21,
Could you explain, with more details, how you calculate the value in the column Exception?
Best regards.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |