Forum Discussion

sqldev2017's avatar
sqldev2017
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    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 )
    )
MFelix's avatar
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's avatar
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's avatar
    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 )
    )
    • sqldev2017's avatar
      sqldev2017
      Microsoft Employee
      7 years ago

      That worked like a Charm. 

       

      Thank You.

       

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

       

      This worked.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

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