Need Urgent help on DAX calculated column

[Vinay07]

Hi All,

Please help me the DAX calculated column in the below requirement. I am unable to get exact solution with this requirement it's very urgent. I tried different ways

Requirement:

1. Good Case 1 : When two SCID_Policy were assigned to two different NewSCV_SCID_POST (Post_scid 000 & 001) but in JCUS_Customer_Identifier assigned (Unconfirmed flag : 0)
   
2. Bad case 1  : When two SCID_Policy were assigned to one single New SCV_SCID_POST (POST_SCID: 1)while in the two different JCUS_Customer_Identifier assigned (Unconfirmed_Customer_Flag:0)

Below are the sample records and column names uploaded to be work on PBI. Please help

largeProductCode JCUS_Policy JCUS_Customer_Identifier Unconfirmed_Customer_Flag SCID_Policy NewSCV_SCID_POST Status
500 6014232 10000115001 0 63855098 0
500 6014232 10000115001 0 63855098 0
500 6641238 10000204001 0 63712575 0
500 6641238 10000204001 0 63712575 0 Good Case 1
500 4610051 10000204001 0 63693665 1 Good Case 1
500 7131464 10000278001 1 63712225 1
500 7131464 10000280001 1 63712224 1
500 7131464 10000280001 1 63712225 1
500 7144856 10000281001 1 63850097 1
500 7144856 10000281001 0 63850097 1 Bad Case 1
501 9921395 10000022001 0 63945420 1 Bad Case 1
501 9921395 10000022001 0 63945420 1
501 9914410 10000020001 1 63734551 1
501 9914410 10000020001 1 63734551 1

[Anonymous]

@Vinay07 ,hello @BeaBF thank you for your prompt reply!
For bad case，please try as following：

Column2 = 
VAR CurrentRow = 'Table'[SCID_Policy]
VAR PreviousSCID = CALCULATE(
                    MAX('Table'[SCID_Policy]),
                    FILTER(
                        ALL('Table'),
                        'Table'[JCUS_Customer_Identifier] <> EARLIER('Table'[JCUS_Customer_Identifier]) &&
                        'Table'[Unconfirmed_Customer_Flag] = EARLIER('Table'[Unconfirmed_Customer_Flag]) &&
                        'Table'[Unconfirmed_Customer_Flag] =0&&
                        'Table'[SCID_Policy] <> CurrentRow &&
                        'Table'[NewSCV_SCID_POST] = EARLIER('Table'[NewSCV_SCID_POST])&&
                        'Table'[NewSCV_SCID_POST]=1 
                    )
                )
var calc =
    IF(
        'Table'[Unconfirmed_Customer_Flag] = 0 &&
        CurrentRow <> BLANK() &&
        PreviousSCID <> BLANK() &&
        CurrentRow <> PreviousSCID,
        "Bad Case 1",
        BLANK()
    )
return calc

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BeaBF]

@Vinay07 Hi! it's not clear the expected output, can you explain it better?

BBF

[Vinay07]

Hi @BeaBF 

Here are the detail explination about assignment in the below screenshot and I have added colors for easy understand.

For  Good Case 1 : When two SCID_Policy were assigned to two different NewSCV_SCID_POST (Post_scid 000 & 001) but in JCUS_Customer_Identifier assigned (Unconfirmed flag : 0)

For Bad case 1  : When two SCID_Policy were assigned to one single New SCV_SCID_POST (POST_SCID: 1)while in the two different JCUS_Customer_Identifier assigned (Unconfirmed_Customer_Flag:0)

[BeaBF]

@Vinay07 ok, on the data you paste, the only labels that you expect as output are those in field status? no more?

BBF

[Vinay07]

Hi @BeaBF 

Yes the status field is "Good case 1" and "Bad case 1" only.

[BeaBF]

@Vinay07 ok, which is the difference between rows 3 and 4 in your example?
Status
500 6014232 10000115001 0 63855098 0
500 6014232 10000115001 0 63855098 0
500 6641238 10000204001 0 63712575 0
500 6641238 10000204001 0 63712575 0 Good Case 1
500 4610051 10000204001 0 63693665 1 Good Case 1

because i created a formula that returns Good Case 1 in correct way, but also in row 3.

I need to understand the difference.

BBF

[Vinay07]

Hi @BeaBF 

We have to Skip the 3rd record it's duplicate for particular policy. Can you share me the formula. I will test it.

[BeaBF]

@Vinay07 This is the code only for Good Case 1:

Good Case 1 =

VAR CurrentRow = 'YourTable'[SCID_Policy]

VAR PreviousSCID = CALCULATE(

                    MAX('YourTable'[SCID_Policy]),

                    FILTER(

                        ALL('YourTable'),

                        'YourTable'[largeProductCode] = EARLIER('YourTable'[largeProductCode]) &&

                        'YourTable'[JCUS_Customer_Identifier] = EARLIER('YourTable'[JCUS_Customer_Identifier]) &&

                        'YourTable'[Unconfirmed_Customer_Flag] = 0 &&

                        'YourTable'[SCID_Policy] <> CurrentRow &&

                        'YourTable'[NewSCV_SCID_POST] <> EARLIER('YourTable'[NewSCV_SCID_POST])

                    )

                )

var calc =

    IF(

        'YourTable'[Unconfirmed_Customer_Flag] = 0 &&

        CurrentRow <> BLANK() &&

        PreviousSCID <> BLANK() &&

        CurrentRow <> PreviousSCID,

        "Good Case 1",

        BLANK()

    )

return calc

BBF

[Vinay07]

Hi @BeaBF 

Thank you so much. I will test and let you know. How about Bad case 1 ? Is same calculation working on Bad case 1 also ?

[BeaBF]

@Vinay07 The construction is the same, but with different conditions. While you test the Good case, i'll make the Bad case formula.

BBF

[Vinay07]

Hi @BeaBF 

I tested the Dax calculation got the result, could you please share Bad Case 1 calculation also.

[BeaBF]

@Vinay07 can you please re-explain the difference between Good Case and Bad Case?

and paste the correct sample data on which can I make some tests. 

Thx,

BBF

[Vinay07]

Hi @BeaBF 

Here I am unable to upload the file or table to share sample data 😞 

[BeaBF]

@Vinay07 You have to upload the file in drive and then paste here the link.

BBF