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
dsandip
Frequent Visitor

CALCULATE function is not working

Hi,

I using below DAX:

Cureent Quarter Sales = CALCULATE([Sales Amount],
    FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)
)
and want to calculate values for [Current QTD] = 1 only and for other rows it should return 0, but it return same values for all the rows. Where I am wrong ?
Below is the table sample: it should show 1732.91 where [Current QTD] =1 as I use filter condition. FOr other rows it should show 0.
dsandip_0-1680176494910.png

 

Thanks,

Sandip

1 ACCEPTED SOLUTION

@dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@dsandip You should be able to do this:

 

Cureent Quarter Sales = CALCULATE([Sales Amount], 'Calendar'[Current QTD] = 1)

or:

Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
RETURN
  __Result

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi,

Your first and 2nd Measure DAX is showing below result which is incorrect:

dsandip_0-1680180200770.png

Measure 3 =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
)
and 
Measure 2 =
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, [Sales Amount], 0 )
RETURN
  __Result
 
Thanks,
Sandip

@dsandip OK, it's incorrect but what is your definition of correct? Maybe this?

Current Quarter Sales = 
  VAR __Current = MAX( 'Calendar'[Current QTD] )
  VAR __Result = IF( __Current = 1, CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1)), 0 )
RETURN
  __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Yes, it is working now. But I wonder that why it is not working for below DAX:

Measure 5= CALCULATE([Sales Amount], FILTER(ALL('Calendar'), 'Calendar'[Current QTD] = 1))
like below:
dsandip_0-1680186319549.png

So, we need to use a if() with condition to show values for current quarter only and others to show 0. right?

ppm1
Solution Sage
Solution Sage

Please try adding KEEPFILTERS to your measure.

 

Current Quarter Sales =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
)

 

Pat

Microsoft Employee
dsandip
Frequent Visitor

hi,

I think KEEPFILTERS() function only show related result, leaving every row value blank as expected, but I want the summation of JAN, Feb, March 2023 in one row, so it show like below:

Measure 3 =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( 'Calendar'[Current QTD] = 1 )
)
dsandip_1-1680180644729.png

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

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