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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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