topic Re: Need Urgent help on DAX calculated column in DAX Commands and Tips

Need Urgent help on DAX calculated column

Anonymous — Wed, 17 Jul 2024 12:29:55 GMT

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:

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)
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

Re: Need Urgent help on DAX calculated column

BeaBF — Wed, 17 Jul 2024 12:40:35 GMT

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

BBF

Re: Need Urgent help on DAX calculated column

Anonymous — Wed, 17 Jul 2024 12:55:19 GMT

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)

Re: Need Urgent help on DAX calculated column

BeaBF — Wed, 17 Jul 2024 13:14:16 GMT

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

BBF

Re: Need Urgent help on DAX calculated column

Anonymous — Thu, 18 Jul 2024 04:32:01 GMT

Hi BeaBF

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

Re: Need Urgent help on DAX calculated column

BeaBF — Thu, 18 Jul 2024 07:15:49 GMT

Anonymous 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

Re: Need Urgent help on DAX calculated column

Anonymous — Thu, 18 Jul 2024 08:32:16 GMT

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.

Re: Need Urgent help on DAX calculated column

BeaBF — Thu, 18 Jul 2024 08:40:28 GMT

Anonymous 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

Re: Need Urgent help on DAX calculated column

Anonymous — Thu, 18 Jul 2024 08:50:20 GMT

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 ?

Re: Need Urgent help on DAX calculated column

BeaBF — Thu, 18 Jul 2024 09:10:36 GMT

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

BBF

Re: Need Urgent help on DAX calculated column

Anonymous — Thu, 18 Jul 2024 14:40:03 GMT

Hi BeaBF

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

Re: Need Urgent help on DAX calculated column

BeaBF — Thu, 18 Jul 2024 14:43:19 GMT

Anonymous 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

Re: Need Urgent help on DAX calculated column

Anonymous — Fri, 19 Jul 2024 03:54:58 GMT

Hi BeaBF

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

Re: Need Urgent help on DAX calculated column

BeaBF — Fri, 19 Jul 2024 07:05:57 GMT

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

BBF

Re: Need Urgent help on DAX calculated column

Anonymous — Fri, 26 Jul 2024 10:00:17 GMT

Anonymous ,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.