Forum Discussion

kalkhudary's avatar
kalkhudary
Icon for Helper IV rankHelper IV
2 years ago

Multiple Filters from multiple tables with a VAR

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
"dax code"
@Dax
DAX help needed
Filters
Show & Tell

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

    Hope this help, kudos appreciated.

    • kalkhudary's avatar
      kalkhudary
      Icon for Helper IV rankHelper IV
      2 years ago

      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
      • some_bih's avatar
        some_bih
        Icon for Community Champion rankCommunity Champion
        2 years ago

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

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

  • kalkhudary's avatar
    kalkhudary
    Icon for Helper IV rankHelper IV
    2 years ago

    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