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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jeff_Ng
Frequent Visitor

equal "=" operator didn't seem to work on calculate and filter functions

I have problem to apply more than one filter (<filter2>....) to my FILTER and CALCULATE functions using the equal "=" operator. They only take the first filter but not the subsequence filters. 

 

To make it work I had to go opposite way using multiple no equal "<>" operator which make my programme very long. Instead of saying I want to keep 2 things out of 20, now I have to say remove 18 things out of 20. 

 

CALCULATE(<expression>,<filter1>,<filter2>…)

FILTER(<table>,<filter1> && <filter2>) or FILTER(<table>, AND(<filter1>, <filter2>)

 

I working with the latest April 2019 Power BI Desktop version. I am still learning DAX but logic tell me if it can take multiple "<>" conditions it should take multiple "=" conditions and other multiple of other operators.

 

Please help. 

 

1 ACCEPTED SOLUTION

Hi @Jeff_Ng

 

The filter conditions in CALCULATE(<expression>,<filter1>,<filter2>…) are performed with AND operation. 

 

You can modify the Calculate_2 as below: 

 

Calculate_2 = var t=SUMMARIZE(FILTER(ALL('Table1'),Table1[Colour]="Red"|| Table1[Colour]="Blue"),Table1[Colour],"total",[SumX_1])
return SUMX(t,[total])
 
q1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

Hi @Jeff_Ng

 

I have modified the measure below: 

 

Calculate_2 = CALCULATE([SumX_1],FILTER('Table1',Table1[Colour]="Red"|| Table1[Colour]="Blue"))
 
SumX_2 =
SUMX(
FILTER(Table1,Table1[Colour]="Red" || Table1[Colour]="Blue"),
Table1[Num1]*Table1[Num2]
)
 
SumX_3 =
SUMX(
FILTER(Table1, OR(Table1[Colour]="Red", Table1[Colour]="Blue")),
Table1[Num1]*Table1[Num2]
)
 
Please check if they meet your requirements. 
 
q5.PNG
 
Best Regards,
Qiuyun Yu 
Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi @v-qiuyu-msft ,

 

Not quite. I,m expecting Calculate_2 is equal to Calculate_4 which is 34 in all rows. Similarly SumX_2 = SumX_4, and SumX_3 = SumX_5. 

 

In fact my concern is not about the results. I want explaination why multiple filters with "=" operators not working in Case 1 but multiple filters with "<>" operators worked in the Case 2.

 

Sybtax for CALCULATE(<expression>,<filter1>,<filter2>…)

 

Case 1, Calculate_2 = CALCULATE([SumX_1],Table1[Colour]="Red", Table1[Colour]="Blue")
Case 2, Calculate_3 = CALCULATE([SumX_1],Table1[Colour]<>"Red", Table1[Colour]<>"Blue")
 

Hi @Jeff_Ng

 

The filter conditions in CALCULATE(<expression>,<filter1>,<filter2>…) are performed with AND operation. 

 

You can modify the Calculate_2 as below: 

 

Calculate_2 = var t=SUMMARIZE(FILTER(ALL('Table1'),Table1[Colour]="Red"|| Table1[Colour]="Blue"),Table1[Colour],"total",[SumX_1])
return SUMX(t,[total])
 
q1.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jeff_Ng
Frequent Visitor

https://1drv.ms/u/s!AjfbxnAiqgTKoBLfbs757sV5Gup6 

 

Hope it works. Any problem please let me know. 

v-qiuyu-msft
Community Support
Community Support

Hi @Jeff_Ng

 

If the report doesn't contain any sensitive data, you can upload it to your OneDrive and paste the share link here. 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jeff_Ng
Frequent Visitor

Hi @v-qiuyu-msft ,

 

Thanks for taking time to response. Attached is the pbix file with ficticius data with some of my measures explaning the problems I mentioned above. 

 

Please explain why "=" operator is not working in measures 

  • Calculate_2
  • SumX_2
  • SumX_3

ohh..How do I attach file?

v-qiuyu-msft
Community Support
Community Support

Hi @Jeff_Ng

 

Is it possible for you to share a pbix file to clarify the issue? Please remove sensitive data in the report before you share it. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors