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] )
    )
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi BaldAccountant 
My understanding:
1. 'Multi' is used to select between different measures.

2. Group is used for slicing and dicing.

3. Data2 is the main table with all the data.

4. No relationships.

Please correct me if I'm wrong.
My guess would be that ALLSELECTED ( Data2 ) is creating this issue. However, won't be able to provide a concerete solution without having the complete picture. Would you please share a screenshot of the Pivot Table blanking out any sensetive data but indicating which column in the pivot table belongs to which table?

  • 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
        )
)
      • BaldAccountant's avatar
        BaldAccountant
        Helper III
        3 years ago

        Sorry, I tried your solution using ALL instead ALLSELECTED, but it gave the same result, all 100%