Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
kalkhudary
Helper III
Helper III

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(paymentbatchtablepaymentbatchtable[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
4 REPLIES 4
kalkhudary
Helper III
Helper III

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 Condition1CALCULATE(DISTINCTCOUNT(claim[Claim]),
FILTER(claimclaim[Total Employee payment Value])<100,
FILTER(paymentbatchtablepaymentbatchtable[Payment file generated] = "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition2CALCULATE(DISTINCTCOUNT(claim[Claim ID]),
FILTER(claimclaim[Total Employee Payment Value])<100,
FILTER(paymentbatchtablepaymentbatchtable[Payment File Cheque Generated]"Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))
 

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

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

 

VAR RESULT = Condition1 + Condition2 +Condition3

 

RETURN
RESULT
some_bih
Super User
Super User

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

Hope this help, kudos appreciated.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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 Condition1CALCULATE(DISTINCTCOUNT(claim[Claim]),
FILTER(claimclaim[Total Employee payment Value])<100,
FILTER(paymentbatchtablepaymentbatchtable[Payment file generated] = "Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))

VAR Condition2CALCULATE(DISTINCTCOUNT(claim[Claim ID]),
FILTER(claimclaim[Total Employee Payment Value])<100,
FILTER(paymentbatchtablepaymentbatchtable[Payment File Cheque Generated]"Yes" && paymentbatchtable[Payment Batch] = SELECTEDVALUE(paymentbatchtable[Payment Batch]))
 

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

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

 

VAR RESULT = Condition1 + Condition2 +Condition3

 

RETURN
RESULT

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.