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

Sum/Filter Help

I'm stumped!   

 

I have the following measure:

 

SAS_Testing:=

SAS-TESTING = 
CALCULATE(SUM(Table1[AMOUNT]),ALL('Name'[NAME]),Table1[Dept]="SAS",
Table1[sales_id]=0,table1[product_id]="GENOH")

Which gives me the exact results i want.  I try using the following (which should provide the maximum value in column product_id for name selected):

 

SAS-Max:=
calculate(max(product_id),allselected('name'[name]))


SAS-TESTING = 
CALCULATE(SUM(Table1[AMOUNT]),ALL('Name'[NAME]),Table1[Dept]="SAS",
Table1[sales_id]=0,table1[product_id]=[sas-max])

 

When I use this i'm greeted with the friendly "a function 'Calculate' has been used in a true/false blah blah blah"  HELP!!!

4 REPLIES 4
Floriankx
Solution Sage
Solution Sage

Hello,

 

Is your product id numeric?

In your first sample you write [product_id]="GENOH".

 

I'm wondering that calculate can handle this equations as filter. I expected, you have to wrap it in a FILTER formula. But obviously that's not an issue.

The column is alphanumeric (but typically contains all text).  I'm wondering if max is the wrong operator to be using to handle strings

@KUNGFUPANDA559

 

Try this

 

SAS-TESTING =
VAR mymeasure = [sas-max]
RETURN
    CALCULATE (
        SUM ( Table1[AMOUNT] ),
        ALL ( 'Name'[NAME] ),
        Table1[Dept] = "SAS",
        Table1[sales_id] = 0,
        table1[product_id] = mymeasure
    )

Regards
Zubair

Please try my custom visuals
Greg_Deckler
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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

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.