Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Kyle21
New Member

Create a Conditional Table that creates a newly summarized table that enforces filters.

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:

 

Kyle21_3-1655974846726.png

 

The rules that I have that each account has to abide by are:

 

Kyle21_1-1655974306361.png

 

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:

 

Kyle21_2-1655974766530.png

 

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

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi  @Kyle21 ,

Here are the steps you can follow:

1. Go to Power query and copy the Table.

vyangliumsft_0-1656383770288.png

2. Select 5 columns in Table2 -- Unpivot columns.

vyangliumsft_1-1656383770291.png

3. In the table formed by Unpivot columns, select [Value] – Remove.

vyangliumsft_2-1656383770292.png

4. Select [Attribute] – Remove Duplicates.

vyangliumsft_3-1656383770292.png

Result:

vyangliumsft_4-1656383770293.png

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:

vyangliumsft_5-1656383770294.png

 

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi  @Kyle21 ,

Here are the steps you can follow:

1. Go to Power query and copy the Table.

vyangliumsft_0-1656383770288.png

2. Select 5 columns in Table2 -- Unpivot columns.

vyangliumsft_1-1656383770291.png

3. In the table formed by Unpivot columns, select [Value] – Remove.

vyangliumsft_2-1656383770292.png

4. Select [Attribute] – Remove Duplicates.

vyangliumsft_3-1656383770292.png

Result:

vyangliumsft_4-1656383770293.png

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:

vyangliumsft_5-1656383770294.png

 

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

ManguilibeKAO
Resolver I
Resolver I

Hi Kyle21,

 

Could you explain, with more details,  how you calculate  the value in the column Exception?

 

Best regards.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.