cancel
Showing results for 
Search instead for 
Did you mean: 
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

@ 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!:
Mastering Power BI 2nd Edition

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

 

 


@ 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!:
Mastering Power BI 2nd Edition

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

@ 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!:
Mastering Power BI 2nd Edition

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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors