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

As a side note, my actual spreadsheet had more group options and more measures.  Most of the group options were in seperate linked tables (Financial Class was the only one on the data table).  I am not sure if this is the best way to do this, but I added these group options to the data table using the related  function.  if there is a better way to do this, please let me know.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    BaldAccountant 

    You can use RELATED indide the FILTER function to filter the Dimension Tables likr this


    VAR GroupValues =
    VALUES ( Groups[Group Values] )
    VAR FilterTable =
    FILTER (
    Data,
    CONTAINS ( GroupValues, Groups[Group Values], Data[FC] )
    || CONTAINS ( GroupValues, Groups[Group Values], Data[Specialty] )
    || CONTAINS ( GroupValues, Groups[Group Values], RELATED ( DimTable[Column] ) )
    )