topic Re: Need Help with Measure in DAX Commands and Tips

Need Help with Measure

Anonymous — Wed, 29 Jan 2020 17:19:52 GMT

I am having trouble getting a measure to work. I have two measures that I am trying to get to work together. The first calculates the maximum value in a column (which will tell me the month #). And then the second measure is supposed to sum a column for all values <= to the maximum month value (that will tell me the current reporting month.) Here are my measures...

This measure returns the # "1":

Current Report Month:=CALCULATE(
MAX(CapitalReportData[PERIOD]),
FILTER(CapitalReportData,CapitalReportData[COST TYPE]="PO")
)

This measure is returning "0" but it should return the sum of FY BUD for months <=1 (Current Report Month)

YTD Budget:=CALCULATE(
SUM(CapitalReportData[FY BUD]),
FILTER(CapitalReportData,CapitalReportData[PERIOD]<=[Current Report Month])
)

When I re-write the 2nd measure as follows, I get the correct value....

YTD Budget:=CALCULATE(
SUM(CapitalReportData[FY BUD]),
FILTER(CapitalReportData,CapitalReportData[PERIOD]<=1)
)

So my question is, why won't the "YTD Budget" measure return a value when using the "Current Report Month" measure?

Thanks!

Re: Need Help with Measure

SQLbyoBI — Wed, 29 Jan 2020 20:16:36 GMT

The 2nd way isn't working because you don't have a separate date table 😉

More specifically, in the second measure...

YTDBudget :=
CALCULATE(
    SUM( CapitalReportData[FY BUD] ),
    FILTER(
        CapitalReportData,
        CapitalReportData[PERIOD] <= [Current Report Month]
    )
)

...when the first measure i.e. [Current Report Month] gets executed from within the second measure, it's being executed in a nested row-context... so really it looks like this...

YTDBudget :=
CALCULATE(
    SUM( CapitalReportData[FY BUD] ),
    FILTER(
        CapitalReportData,
        CapitalReportData[PERIOD] <= 
            /* first measure */
            CALCULATE(
                MAX( CapitalReportData[PERIOD] ),
                FILTER(
                    CapitalReportData,
                    CapitalReportData[COST TYPE] = "PO"
                )
            )
    )
)

A better approach is to use a separate date table with a 1-M relationship to your CapitalReportData table. Then change your measures to the following...

Current Report Month :=
CALCULATE(
    MAX( 'Calendar'[PERIOD] ),
    FILTER(
        CapitalReportData,
        CapitalReportData[COST TYPE] = "PO"
    )
)

...and...

YTDBudget :=
VAR __curReportMth = [Current Report Month]

VAR __retVal = 
CALCULATE(
    SUM( CapitalReportData[FY BUD] ),
    FILTER(
        ALL( 'Calendar') , /* need remove the filter on Period to get YTD */
        Calendar[PERIOD] <= __curReportMth
    )
)
RETURN __retVal

HTH

Re: Need Help with Measure

JarroVGIT — Wed, 29 Jan 2020 20:19:16 GMT

Hi Anonymous ,

Your first measure is evaluated in the context of the second measure. If the second measure is evaluated in a filtered context based on for example Period = 2, then the first measured will evaluate to 2 and not to the overall MAX of period in the table.

I think this will remedy this for you:

Current Report Month:=CALCULATE( MAX(CapitalReportData[PERIOD]), FILTER(ALL(CapitalReportData),CapitalReportData[COST TYPE]="PO") )

By using ALL() in your FILTER(), the evaluation context is changed and now this measure will always result in the same value, regardless of the context it is evaluated in (e.g. a matrix visual for example).

Hope that this makes sense, let me know if this helped you!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Need Help with Measure

Anonymous — Fri, 31 Jan 2020 21:39:35 GMT

Thanks for both suggestions. Since the 2nd one was easier to implement, I started with that one and it worked. (Adding ALL to the Filter of the Measure 1. I will need to think about why that worked!

And I understand the point about the date table. The query that generates this data already computes the period based on the date but I guess doing that in the model is a better solution so I will look at that going forward.

Thanks again to both!

Re: Need Help with Measure

Anonymous — Fri, 31 Jan 2020 21:44:17 GMT

One more question on this... Is there a way for me to see what the Filter was returning in my original formula? I think that might help me better understand the fix.

Thanks!