1

I have a fact table (MORT) and a dimension table (GEO) in PowerPivot (2016). There are two joins between the tables with two columns in MORT relating to one column in GEO. We can call one join RES and the other REG. I have a large number of measures and I want to allow the user to quickly change between seeing the measures using the RES and REG relationships - essentially I want to be able to switch the active relationship (referred to as switch in the code). This would probably be based on a detached slicer.

I have tried nesting an if statement and various ways of defining a variable to use in a CALCUATE but either have the error that USERELATIONSHIP can only be used in a calculate (in the case of the if) or that USERELATIONSHIP only accepts a column reference (in the case of the var).

IF STATEMENT

MEASURE:= CALCULATE(COUNT(MORT[ID]), GEO, 
IF(LASTNONBLANK(SWITCH,1)="RES",
   USERELATIONSHIP(MORT[RES], GEO[AREA]), 
   USERELATIONSHIP(MORT[REG], GEO[AREA]))

VAR

MEASURE:=

VAR switch = IF(LASTNONBLANK(Switch,1)="RES", MORT[RES], MORT[REG])

RETURN
CALCULATE(COUNT(MORT[ID]), GEO, USERELATIONSHIP(switch, GEO[AREA])

I could create every measure with an if statement at the start to check the value of switch but this creates a lot of duplicate code.

I want some way for the end user to change the active relationship in a measure but ideally without a lot of duplicated code.

will1329
  • 173
  • 2
  • 14

1 Answers1

1

How about like this?

MEASURE :=
IF (
    LASTNONBLANK ( Switch, 1 ) = "RES",
    CALCULATE ( COUNT ( MORT[ID] ), GEO, USERELATIONSHIP ( MORT[RES], GEO[AREA] ) ),
    CALCULATE ( COUNT ( MORT[ID] ), GEO, USERELATIONSHIP ( MORT[REG], GEO[AREA] ) )
)

Edit:

In your comment, you said you tried this:

M :=
VAR calc =
    FILTER ( MORT, MORT[CAUSE] >= "I" && 'Remake Data'[CAUSE] <= "I9" )
VAR select =
    IF (
        HASONEVALUE ( 'SWITCH'[Switch] ),
        LASTNONBLANK ( 'SWITCH'[Switch], 1 ),
        "ERROR"
    )
RETURN
    IF (
        select = "RES",
        CALCULATE ( COUNTROWS ( calc ), USERELATIONSHIP ( MORT[RES], GEO[Area] ) ),
        IF (
            selection = "REG",
            CALCULATE ( COUNTROWS ( calc ), USERELATIONSHIP ( MORT[REG], GEO[Area] ) ),
            "ERROR"
        )
    )

There are a few problems with this, but mainly, if you define something as a variable, then it's constant and won't be affected by other things within a CALCULATE. Try the following instead.

Define a new measure:

CountRowsMeasure = 
COUNTROWS ( FILTER ( MORT, MORT[CAUSE] >= "I" && 'Remake Data'[CAUSE] <= "I9" ) )

Then use that measure within your M measure:

M =
VAR select = SELECTEDVALUE( 'SWITCH'[Switch], "ERROR" )
RETURN
SWITCH (
    select,
    "RES", CALCULATE ( [CountRowsMeasure], USERELATIONSHIP ( MORT[RES], GEO[Area] ) ),
    "REG", CALCULATE ( [CountRowsMeasure], USERELATIONSHIP ( MORT[REG], GEO[Area] ) ),
    "ERROR"
)
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Hi @Alexis I had thought about doing that (2nd to last paragraph) but some of my calculate steps are quite long/intricate so to avoid being too duplicative I wondered if there was a cleaner way - this might be the best way though. Thanks for the quick reply. – will1329 Apr 18 '19 at 10:11
  • If instead of `COUNT(MORT(ID))` you have a more complex expression, then you can abstract that away as its own measure to prevent some duplication of code. – Alexis Olson Apr 18 '19 at 12:38
  • Thanks thats a good idea but I can't get it to get it to work (I think because GEO is part of a many-to-many relationship [http://powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many]). Formula below if you had ideas: `M:= VAR calc = FILTER(MORT, MORT[CAUSE]>="I" && 'Remake Data'[CAUSE]<="I9") VAR select = IF(HASONEVALUE('SWITCH'[Switch]),LASTNONBLANK('SWITCH'[Switch],1),"ERROR") RETURN IF(select="RES",CALCULATE(COUNTROWS(calc), USERELATIONSHIP (MORT[RES], GEO[Area])), IF(selection="REG",CALCULATE(COUNTROWS(calc), USERELATIONSHIP (MORT[REG], GEO[Area])),, "ERROR"))` – will1329 Apr 24 '19 at 13:47
  • Create a new measure, not a new variable within your current measure. Please see my edit above. – Alexis Olson Apr 24 '19 at 14:08