Forum Discussion

Switto's avatar
Switto
Helper IV
5 years ago
Solved

Visualizing with static and dynamic measures

Hi Team,

I am working on a visual to compare the FTEs for the Base and Current year.  Base year FTE will never change however current year FTE will change(with slicer).

 

For the base FTE measure:

mBase_FTE = CALCULATE(SUM('Region Penetration'[Total FTE]),
USERELATIONSHIP(Dim_Region[Region],'Region Penetration'[Region]))
 
For the current year measure:
mCurrent_FTE = SUM(Table[Current FTE])
 
after putting both the measures in the value field, it is coming as per the requirement however, while selecting the FY Year or other slicers, the "mBase_FTE" measure is also changing.
Is there a way to make the "mBase_FTE" static and don't change with slicers?
Thanks
S
Need Help
  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    5 years ago

    Do you use a separate calendar table? If yes, here's a suggestion:

    mBase_FTE =

    CALCULATE(

        SUM('Region Penetration'[Total FTE]),
        USERELATIONSHIP(Dim_Region[Region],'Region Penetration'[Region])),

        ALL( 'Calendar' )

    )

     

    If not (you should), then replace Calendar with whatever contains your year column, ie. 'Region Penetration'[FY Year]

     

    Keep in mind that USERELATIONSHIP can result in trouble if you use RLS.

2 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Continued Contributor
    5 years ago

    Do you use a separate calendar table? If yes, here's a suggestion:

    mBase_FTE =

    CALCULATE(

        SUM('Region Penetration'[Total FTE]),
        USERELATIONSHIP(Dim_Region[Region],'Region Penetration'[Region])),

        ALL( 'Calendar' )

    )

     

    If not (you should), then replace Calendar with whatever contains your year column, ie. 'Region Penetration'[FY Year]

     

    Keep in mind that USERELATIONSHIP can result in trouble if you use RLS.