Forum Discussion
Compound measures losing filters
Hi, I'm building a BI model on both GL data and data from time reports.
My goal is to have one measure that have a default value but can be changed by slicers for example "version"
In our case version can be "Actual", "Forecast", "Budget", "Last year", default should be "Actual" (VersionId 0)
I solved it with this formula.
Hours Total Current =
IF(NOT(OR(ISFILTERED('Version'[Version Name]),ISFILTERED('Version'[Version]))),
CALCULATE(
CALCULATE(SUM('Project Data'[NumberTotal]),'Project Data'[transtype]=1) ,FILTER('Version','Version'[VersionId]=0)),
CALCULATE(SUM('Project Data'[NumberTotal]),'Project Data'[transtype]=1) )
Side question, tried doing it like this, but then it didn't work, why?
VAR Hours = CALCULATE(SUM('Project Data'[NumberTotal]),'Project Data'[transtype]=1)
RETURN
IF(NOT(OR(ISFILTERED('Version'[Version Name]),ISFILTERED('Version'[Version]))),
CALCULATE( Hours ,FILTER('Version','Version'[VersionId]=0)), Hours )
I then use this measure in a switch to choose between current month or YTD:
Hours Total = SWITCH(MIN('Time Function'[TimeFunctionId]),
1, [Hours Total Current],
2,'Project Data'[Hours Total YTD])
So far so good, everything works.
My problem is when I want to use [Hours Total] in another measure, like this:
Hours Absent =
CALCULATE( [Hours Total], Filter('Project Data','Project Data'[projectclasstype]>3))
Then the default Version isn't working so if none is selected i get the sum of all versions. The YTD option isn't working either I always get Current even when sliced on YTD.
What am I doing wrong?
2 Replies
- amitchandak
Super User
Anonymous , check if this can help
Hours Total = SWITCH(selectedvalue('Time Function'[TimeFunctionId],1),
1, [Hours Total Current],
2,'Project Data'[Hours Total YTD])- AnonymousNot applicable
Thanks, but the problem persists.