cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

Thanks,

Sandip

1 ACCEPTED SOLUTION
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi,

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

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
Super User

@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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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:

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

Solution Sage

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

Pat

Microsoft Employee
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 )
)

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors