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

[Kyle21]

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

[Anonymous · Accepted 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

[ManguilibeKAO]

Hi Kyle21,

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

Best regards.