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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
chakrabmonoj
Helper II
Helper II

combine CALCULATE with SWITCH to filter multiple criteria for the same field

I have used the calculate function along with switch to use multiple criteria against the same field value as below :

 

H1 = calculate(sum('ytd dec''16'[VALUE]),switch('ytd dec''16'[MONTH],"jul",true(),false()))

 

but this formula does not seem to return any value - i intend to extend it to several other filters and was testing the formula with just one month as the criterion

 

can I get some urgent help on this? thank you

 

1 ACCEPTED SOLUTION

Sean - thanks. I solved the problem with your help. 

View solution in original post

8 REPLIES 8
Sean
Community Champion
Community Champion

Have you tried a different month? (Do you have any Filters/Slicers maybe excluding the July values?)

 

I assumed this is a typo => H1 = calculate(sum('ytd dec''16'[VALUE]),switch('ytd dec''16'[MONTH],"jul",true(),false()))

Sean,

 

I guess we are separated by a few time zones so my delay in checking your reply. Thanks for the quick revert. As for your queries :

 

1. I have used no slicers and hence Jul is not sliced out

2. Have tried using different months too, with the same result - in fact I want to sum all values for the year 2015, if the months match any of jul till dec

3. H1 - is the name of the measure, which is my given name to identify this measure

4. Are you able to spot any typo in my formula? The formula reported no error, by the way

 

any help highly appreciated

 

thanks again

The formula works fine for me!

I marked the quotes in red above because I had to remove those when testing.

I was getting a message that this is not allowed in the Name of a Table when I tried to rename a table in PBI.

However I just tested Importing a table that has " in the Name and that was fine!

But again the Measure works for me!

EDIT: The Warning Message actually only shows up when you try to Rename a Table!

Rename Table.png

Sean - thanks again. Wondering if the formula is working for you, why is it not returning any value for me?

 

would attaching the file itself help?

If your data is not sensitive and/or you can share the file go ahead.

Since you currently have only 1 condition in the SWITCH have you tested without it and just setting the month equal to Jul?

Sean - i will check without the swicth as per your advice.

 

Is there a place I can send the data to?

You can upload the file to OneDrive or Drop Box and share the link here Smiley Happy

Sean - thanks. I solved the problem with your help. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.