cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Help with Simple DAX formula

 

I am doing something like this - 

 

PreviousMonth = FORMAT(CALCULATE(MIN(DimDate[Month]), FILTER(DimDate, DimDate[Month_Year] = [SelectedMonthYear]) ), "String")
 
-  Min(DimDate[Month])  returns a string ... I can see it returns the right string.
 
But when i now use this calculated "Measure"  (PreviousMonth) - in another formula , the result of that formula returns BLANK ....  When I replace this measure (in that other formula), with a hard coded string , that formula returns the right result ! 
 
What am i doing wrong ?   
 
I have tried every kind of thing, it doesn't work ! 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Help with Simple DAX formula

@sqldev2017

 

Try storing the MEASURE value in a variable first

 

Revenue =
VAR myMEASURE = [MonthYear]
RETURN
    CALCULATE (
        SUM ( fact[Revenue] ),
        FILTER ( fact, fact[fiscalMonth] = myMEASURE )
    )
Try my new Power BI game Cross the River

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Re: Help with Simple DAX formula

Hi @sqldev2017,

 

What do you want to use this measure for? what is the context and calculation you arre making on the other measure where this one is used?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: Help with Simple DAX formula

 

I simply want to return one string value from a table, based on a filter....  That filter will always return one string value (for that table).

 

I understand CALCULATE is only for numeric measures... 

 

But in this instance CALCULATE also seems to return a string ! 

Highlighted
Super User III
Super User III

Re: Help with Simple DAX formula

Hi @sqldev2017,

Calculate is not only for numeric it can be used in very different ways and the result will be variable text, numbers, etc.


You say in your post that you want to use the measure you calculated in another measure what is exactly your needs? Can you share sample data, setup expected result.

Regards

Mfelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: Help with Simple DAX formula

I am doing something pretty straightforward. 

 

My first "calculated measure"  "MonthYear" returns a value say =  "December 2018"

 

 

I use this calculated measure "MonthYear" in a different calculated measure as a Filter parameter - 

 

"Revenue" = CALCULATE(SUM(Revenue), FILTER("fact", fact.fiscalMonth = MonthYear)) 

 

I can verify that MonthYear measure actually returns "December 2018"

 

But Revenue measure returns a blank value.

 

When I replace the MonthYear parameter , with a hard coded value 

 

"Revenue" = CALCULATE(SUM(Revenue), FILTER("fact", fact.fiscalMonth = "December 2018" )) - Now this returns proper revenue value.

 

I do not understand why this is happening !  This is just so Bizzare.

 

 

Highlighted
Super User III
Super User III

Re: Help with Simple DAX formula

@sqldev2017

 

Try storing the MEASURE value in a variable first

 

Revenue =
VAR myMEASURE = [MonthYear]
RETURN
    CALCULATE (
        SUM ( fact[Revenue] ),
        FILTER ( fact, fact[fiscalMonth] = myMEASURE )
    )
Try my new Power BI game Cross the River

View solution in original post

Highlighted
Helper I
Helper I

Re: Help with Simple DAX formula

That worked like a Charm. 

 

Thank You.

 

Honestly,  I was trying to do this FORMAT(Measure, "String")  , that was not working.

 

This worked.

Highlighted
Super User III
Super User III

Re: Help with Simple DAX formula

@sqldev2017

 

Glad it worked.

 

When you use a MEASURE inside a FILTER function, it is evaluated for each row of the Table inside FILTER function (in your case it is FACT table)

That's why we have to store its value first in a variable

 

 FILTER ( fact, fact[fiscalMonth] = [Measure] )
    )

 

Try my new Power BI game Cross the River

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors