Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • Anonymous , check if this can help

    Hours Total = SWITCH(selectedvalue('Time Function'[TimeFunctionId],1),
    1, [Hours Total Current],
    2,'Project Data'[Hours Total YTD])

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks, but the problem persists.