URGENT help on DAX calculated column for different cases to display in power bi

[Vinay07]

Hi All,

Please find the below screenshot of sample data, need help on the different cases reasons for the below data to display in different column reasons. Please help it's very urgent. I have tried different approches but unable to reach solutions.

Below are the requirements need to work on the DAX calculated column need to display in different columns: 

CASE 1: "Two SCIDs merged into a confirmed SCID"

CASE 2: "Confirmed SCID has been Unmerged"

CASE 3: "Insured Info mismatch"

CASE 1: (When two different policies were assigned to two different SCID_POST (000 and 001), but under NSCID both SCID_POST (000 and 001) merged into a duplicate NSCID and Customer Flag: ""0"" ) Need to display as ""Two SCIDs merged into a confirmed SCID"

CASE 2: (When two different policies were assigned to SCID_POST (000), but under different NSCID is assigned to Customer Flag: ""0"") Need to Display as "" Confirmed SCID has been Unmerged"

CASE 3: (When Client Type: Insured is the same(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned different ID numbers and Customer Flag is: ""0"" and ""1"") Vise Versa (When Client Type: Insured is the different(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned same ID numbers and Customer Flag is: ""0"") 
Need to display as ""Insured Info Mismatch"

Expected OUTPUT: Here is the expected output to be display in POWER BI. Please help.

Sample Data Reocrds: Here is the sample records can work for this expected OUTPUT.

NOTE: For SCID_POST (000 and 001) from power query editor can achieve with help of custom column: =Number.ToText([SCID_POST],"d3")

Product Code Policy Client Type NSCID Unconfirmed Customer Flag SCID_PRE SCID_POST
502 6369188 Insured 10001135001 0 63948950 001
500 7417992 PolicyHolder 10001135001 0 63948950 000
500 8567299 Insured 10001059001 0 63950734 000
500 8439184 Insured 10001059001 0 63950734 001
501 4041712 Insured 10001225001 0 63949029 000
501 4041711 Insured 10001225001 0 63949029 001
484 3602011 Insured 10001327001 0 20030916 000
484 3603002 Insured 10001328001 0 20030916 000
484 3603002 Insured 10001328001 0 20030916 000
484 3603005 Insured 10001329001 0 20030916 000
501 9922164 PolicyHolder 10000898001 0 63950560 000
501 9922163 Insured 10000247901 0 63950560 000
501 9922162 Insured 10000898001 0 63950560 000
501 9922162 Insured 10000898001 0 63950560 000
501 9922163 PolicyHolder 10000898001 0 78526740 001
500 7143845 Insured 10000932001 0 63685171 001
500 7143845 PolicyHolder 10000937001 1 63685171 001
500 1266967 PolicyHolder 10001238001 0 63691789 001
500 1266967 Insured 10001242001 1 63691789 001
501 9922151 Insured 10001210001 0 63950555 000
500 7527515 Insured 10001216001 0 63949292 000

[Vinay07]

Hi @bhanu_gautam ,

Thank you so much for your help.

I have tried with your solution in DAX calculated column. Please find the below screenshot.

Here CASE 1 and CASE 3 values are effecting expect CASE 2. Please check it.

If you've worked on your PBIX file please share it. Thank you!

[bhanu_gautam]

@Vinay07 , Try using this or share sample data in format which can be copied in excel

Reason =
VAR SCID_PRE = [SCID_PRE]
VAR SCID_POST = [SCID_POST]
VAR NSCID = [NSCID]
VAR CustomerFlag = [Customer Flag]
VAR ClientType = [Client Type]
VAR Policy = [Policy]

RETURN
SWITCH(
TRUE(),
// CASE 1: Two SCIDs merged into a confirmed SCID
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_POST] IN {"000", "001"} &&
'Table'[NSCID] = NSCID &&
'Table'[Customer Flag] = 0
)
) > 1 && SCID_PRE <> SCID_POST, "Two SCIDs merged into a confirmed SCID",

// CASE 2: Confirmed SCID has been Unmerged
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_POST] = SCID_POST &&
'Table'[NSCID] <> NSCID &&
'Table'[Customer Flag] = 0
)
) > 1 && SCID_PRE = SCID_POST, "Confirmed SCID has been Unmerged",

// CASE 3: Insured Info mismatch
(
(ClientType = "Insured" &&
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_PRE] = SCID_PRE &&
'Table'[SCID_POST] = SCID_POST &&
'Table'[Client Type] = "PolicyHolder" &&
'Table'[Customer Flag] = 1
)
) > 0) ||
(ClientType = "PolicyHolder" &&
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_PRE] = SCID_PRE &&
'Table'[SCID_POST] = SCID_POST &&
'Table'[Client Type] = "Insured" &&
'Table'[Customer Flag] = 0
)
) > 0)
), "Insured Info mismatch",

BLANK()
)

[Vinay07]

Hi @bhanu_gautam  @bhanu_gautam 

I am extreamly sorry for the confusion on the CASE 2 on SCID_POST instead of (001) it should be (000) in the records.

Now Please find out the revised requirement and latest records. Please help.

CASE 1: (When two different policies were assigned to two different SCID_POST (000 and 001), but under NSCID both SCID_POST (000 and 001) merged into a duplicate NSCID and Customer Flag: ""0"" ) Need to display as ""Two SCIDs merged into a confirmed SCID"

CASE 2: (When two different policies were assigned to SCID_POST (000), but under different NSCID is assigned to Customer Flag: ""0"") Need to Display as "" Confirmed SCID has been Unmerged"

CASE 3: (When Client Type: Insured is the same(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned different ID numbers and Customer Flag is: ""0"" and ""1"") Vise Versa (When Client Type: Insured is the different(SCID_PRE and SCID_POST) as policy holder (SCID_PRE and SCID_POST), but under NSCID assigned same ID numbers and Customer Flag is: ""0"") 
Need to display as ""Insured Info Mismatch"

Expected OUTPUT: Here is the revised expected output to be display in POWER BI. Please help.

Here are the Sample Records which is in Excel Output above. Please help.

Product Code Policy Client Type NSCID Unconfirmed Customer Flag SCID_PRE SCID_POST
502 6369188 Insured 10001135001 0 63948950 001
500 7417992 PolicyHolder 10001135001 0 63948950 000
500 8567299 Insured 10001059001 0 63950734 000
500 8439184 Insured 10001059001 0 63950734 001
501 4041712 Insured 10001225001 0 63949029 000
501 4041711 Insured 10001225001 0 63949029 001
484 3602011 Insured 10001327001 0 20030916 000
484 3603002 Insured 10001328001 0 20030916 000
484 3603002 Insured 10001328001 0 20030916 000
484 3603005 Insured 10001329001 0 20030916 000
501 9922164 PolicyHolder 10000898001 0 63950560 000
501 9922163 Insured 10000247901 0 63950560 000
501 9922162 Insured 10000898001 0 63950560 000
501 9922162 Insured 10000898001 0 63950560 000
501 9922163 PolicyHolder 10000898001 0 78526740 001
500 7143845 Insured 10000932001 0 63685171 001
500 7143845 PolicyHolder 10000937001 1 63685171 001
500 1266967 PolicyHolder 10001238001 0 63691789 001
500 1266967 Insured 10001242001 1 63691789 001
501 9922151 Insured 10001210001 0 63950555 000
500 7527515 Insured 10001216001 0 63949292 000

[bhanu_gautam]

@Vinay07 , Try using below DAX to create a calcuated column

Reason =
VAR SCID_PRE = [SCID_PRE]
VAR SCID_POST = [SCID_POST]
VAR NSCID = [NSCID]
VAR CustomerFlag = [Customer Flag]
VAR ClientType = [Client Type]
VAR Policy = [Policy]

RETURN
SWITCH(
TRUE(),
// CASE 1: Two SCIDs merged into a confirmed SCID
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_POST] = SCID_POST &&
'Table'[NSCID] = NSCID &&
'Table'[Customer Flag] = 0
)
) > 1 && SCID_PRE <> SCID_POST, "Two SCIDs merged into a confirmed SCID",

// CASE 2: Confirmed SCID has been Unmerged
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_POST] = SCID_POST &&
'Table'[Customer Flag] = 0
)
) > 1 && SCID_PRE = SCID_POST, "Confirmed SCID has been Unmerged",

// CASE 3: Insured Info mismatch
(
(ClientType = "Insured" &&
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_PRE] = SCID_PRE &&
'Table'[SCID_POST] = SCID_POST &&
'Table'[Client Type] = "PolicyHolder" &&
'Table'[Customer Flag] = 1
)
) > 0) ||
(ClientType = "PolicyHolder" &&
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[SCID_PRE] = SCID_PRE &&
'Table'[SCID_POST] = SCID_POST &&
'Table'[Client Type] = "Insured" &&
'Table'[Customer Flag] = 0
)
) > 0)
), "Insured Info mismatch",

BLANK()
)