Forum Discussion

Microsoft Employee

7 years ago

Solved

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]) ret...

Zubair_Muhammad

7 years ago

Try storing the MEASURE value in a variable first

Revenue =
VAR myMEASURE = [MonthYear]
RETURN
    CALCULATE (
        SUM ( fact[Revenue] ),
        FILTER ( fact, fact[fiscalMonth] = myMEASURE )
    )

Super User

7 years ago

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

Microsoft Employee

7 years ago

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 !

MFelix
Super User
7 years ago
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
- sqldev2017
  Microsoft Employee
  7 years ago
  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.
  - Zubair_Muhammad
    Community Champion
    7 years ago
    sqldev2017
    
    Try storing the MEASURE value in a variable first
    
    Revenue = VAR myMEASURE = [MonthYear] RETURN CALCULATE ( SUM ( fact[Revenue] ), FILTER ( fact, fact[fiscalMonth] = myMEASURE ) )

Related content