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?