topic Re: Multiple Filters from multiple tables with a VAR in DAX Commands and Tips

Multiple Filters from multiple tables with a VAR

kalkhudary — Wed, 27 Sep 2023 03:19:54 GMT

Hello Community,

I have a tricky problem that I am trying to solve related to a DAX. I have daxed the below to calculate the distinct count of claims based on 3 conditions and summing them at the end for whatever applies. However, It is giving me me that it is incorrect. Any idea on what I am missing or perhaps is there a better way to dax it.

#Claims - <100$ =

VAR Condition1= CALCULATE(DISTINCTCOUNT(claim[Claim]),

FILTER(claim, claim[Total Employee payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment file generated] = "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition2= CALCULATE(DISTINCTCOUNT(claim[Claim ID]),

FILTER(claim, claim[Total Employee Payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]= "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

FILTER(claim, claim[Total Employee Money Value(Base)])<100,

FILTER(chequerecord, CONTAINSSTRING(chequerecord[Payment Batch],"EFT")),

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]="Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR RESULT = Condition1 + Condition2 +Condition3

RETURN

RESULT

SUM

Re: Multiple Filters from multiple tables with a VAR

some_bih — Wed, 27 Sep 2023 07:26:57 GMT

Hi kalkhudary there is part "Condition3" but no definition in VAR part.

Hope this help, kudos appreciated.

Re: Multiple Filters from multiple tables with a VAR

kalkhudary — Wed, 27 Sep 2023 12:29:13 GMT

Hello some_bih Apologies, I missed it but orginally it was in my DAX but still giving me an error. Can you check the daxing for me.

#Claims - <100$ =

VAR Condition1= CALCULATE(DISTINCTCOUNT(claim[Claim]),

FILTER(claim, claim[Total Employee payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment file generated] = "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition2= CALCULATE(DISTINCTCOUNT(claim[Claim ID]),

FILTER(claim, claim[Total Employee Payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]= "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition3 = FILTER(claim, claim[Total Employee Money Value(Base)])<100,

FILTER(chequerecord, CONTAINSSTRING(chequerecord[Payment Batch],"EFT")),

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]="Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR RESULT = Condition1 + Condition2 +Condition3

RETURN

RESULT

Re: Multiple Filters from multiple tables with a VAR

some_bih — Wed, 27 Sep 2023 13:15:18 GMT

Hi kalkhudary you will need something "before" FILTER in VAR below

VAR Condition3 = FILTER(claim, claim[Total Employee Money Value(Base)])<100

Re: Multiple Filters from multiple tables with a VAR

kalkhudary — Wed, 27 Sep 2023 13:35:18 GMT

I have made the change below but still not yielding any results. Wondering if I need to fix anything in the logic

#Claims - <100$ =

VAR Condition1= CALCULATE(DISTINCTCOUNT(claim[Claim]),

FILTER(claim, claim[Total Employee payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment file generated] = "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition2= CALCULATE(DISTINCTCOUNT(claim[Claim ID]),

FILTER(claim, claim[Total Employee Payment Value])<100,

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]= "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition3 = CALCULATE(DISTINCTCOUNT(claim[Associtaed Claim ID]), FILTER(claim, claim[Total Employee Money Value(Base)])<100,

FILTER(chequerecord, CONTAINSSTRING(chequerecord[Payment Batch],"EFT")),

FILTER(paymentbatchtable, paymentbatchtable[Payment File Cheque Generated]="Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR RESULT = Condition1 + Condition2 +Condition3

RETURN

RESULT