Forum Discussion

BaldAccountant's avatar
BaldAccountant
Helper III
3 years ago
Solved

Power pivot - Dynamic Measure

I am tyring to create a dynamic measure based on a post on another website in power pivot.  There are two elements: -a table that creates dynamic choice of row values (i.e. parse out by insurance ty...
  • tamerj1's avatar
    tamerj1
    3 years ago

    BaldAccountant 
    I've changed the data model relaying more on DAX. Please refer to attached sample file.

    =
VAR FilterTable =
    FILTER (
        Data,
        CONTAINS ( VALUES ( Groups[Group Values] ), Groups[Group Values], Data[FC] )
            || CONTAINS (
                VALUES ( Groups[Group Values] ),
                Groups[Group Values], Data[Specialty]
            )
    )
RETURN
    SWITCH (
        MIN ( Multi[MSR Number] ),
        1, DIVIDE ( CALCULATE ( [Cases], FilterTable ), [Cases] ),
        2, DIVIDE ( CALCULATE ( [Charges], FilterTable ), [Charges] )
    )
BaldAccountant's avatar
BaldAccountant
Helper III
3 years ago

Here is a real basic example

Data

IDFCSpecialtyCharge

1Blue CrossHand100
2MCRHand200
3Blue CrossFoot300

Intermediary table 1

Fin Class2Fin Class3

Blue CrossBlue Cross
MCRMCR

Intermediary table 2

Spec2Spec3

FootFoot
HandHand

 

Groups

Group nameGroup ValuesFin ClassSpecialty

Fin ClassBlue CrossBlue CrossHand
Fin ClassBlue CrossBlue CrossFoot
Fin ClassMCRMCRHand
Fin ClassMCRMCRFoot
SpecialtyHandHandHand
SpecialtyHandHandFoot
SpecialtyFootFootHand
SpecialtyFootFoot

Foot

Measures table

MeasureMSR Number

Pct of Cases1
Pct of Charges2

Pct of Cases:=DIVIDE([Cases],CALCULATE([Cases],ALLSELECTED(Data)),0)

Pct of Charges:=DIVIDE([Charges],CALCULATE([Charges],ALLSELECTED(Data)),0)

Measure Calc:=switch(TRUE(),min(Multi[MSR Number])=1,[Pct of Cases],min(Multi[MSR Number])=2,[Pct of Charges])

Relationships - I was wrong - measures does not have a relationship

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

 BaldAccountant 
Please try using ALL instead of ALLSELECTED

Cases :=
DIVIDE ( [Cases], CALCULATE ( [Cases], ALL ( Data2 ) ), 0 )

The SWITCH measure can be simplified (just less dax) as follows

MeasureValue5 :=
SWITCH (
    MIN ( Multi[Msr Number] ),
    1, CALCULATE ( FORMAT ( [Current % Cases], "#,#0.0%;(#,#0.0%);-" ), Groups ),
    2, CALCULATE ( FORMAT ( [Avg Charges], "#,#0;(#,#0):-" ), Groups ),
    3, CALCULATE ( FORMAT ( [Cases], "#,#0;(#,#0;-" ), Groups ),
    4, CALCULATE ( FORMAT ( [Current % of Charges], "#,#0.0%;(#,#0.0%);-" ), Groups ),
    5,
        CALCULATE (
            FORMAT ( [Expense % Reimbursement], "#,#0.0%;(#,#0.0%);-" ),
            Groups
        )
)