Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BaldAccountant
Advocate I
Advocate I

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 type or physician specialty or age of patient or patient status

-a measure that allows the user to pick from multiple measures

 

In the table, lets call it groups, there are 4 columns:

-group name(i.e insurance type or specialty

-goup value for each item in the broader group (i.e. Blue Cross or Medicare and Specialty Hand vs Foot )

-two or more columns with every permutation of the group values (i.e. Blue Cross Hands, Blue Cross Foot, Medicare Hands, Medicare Foot

 

The measure is as follows:

Measure Value5:=Switch(TRUE(),min(Multi[Msr Number])=1,CALCULATE(format([Current % Cases],"#,#0.0%;(#,#0.0%);-"),Groups),min(Multi[Msr Number])=2,CALCULATE(format([Avg Charges],"#,#0;(#,#0):-"),Groups),min(Multi[Msr Number])=3,CALCULATE(format([Cases],"#,#0;(#,#0;-"),Groups),min(Multi[Msr Number])=4,CALCULATE(format([Current % of Charges],"#,#0.0%;(#,#0.0%);-"),Groups),min(Multi[Msr Number])=5,CALCULATE(format([Expense % Reimbursement],"#,#0.0%;(#,#0.0%);-"),Groups))

 

The part that is giving me trouble are the Current % of Cases and the Current % of Charges for example Current % Cases:=divide([Cases],CALCULATE([Cases],ALLSELECTED(Data2)),0)

It returns 100% for each row, vs 6% for Foot and 94% Hands.

The dynamic measure works fine if I use a non dynamic row value.  

I know this can be done in the Power BI desktop with parameters, but I tested this setup in the desktop and it did not work there either.

I have a boss that prefers Excel, but would rather just click on a slicer to change the measure or row value vs changing the row value in the pivot table

Any suggestions?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png2.png

=
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] )
    )

View solution in original post

11 REPLIES 11
BaldAccountant
Advocate I
Advocate I

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.

@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] ) )
)

tamerj1
Super User
Super User

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

1.png2.png

=
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] )
    )

Thank you very much!  You are amazing.

This is the first time I have posted a question here.  It's the first time I could not solve something on my own and it felt like I was quitting, but I guess everyone needs a little help from time to time.

 

BaldAccountant
Advocate I
Advocate I

There are relationships betwen the tables, but 1-3 are correct

There are intermediary tables between the Groups FinClass column and Data2 via an intermediary table and between the Groups Specialty column and the Data2 via another intermediary table

 

BaldAccountant_0-1675261756490.pngBaldAccountant_1-1675261792384.png

 

tamerj1
Super User
Super User

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?

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

BaldAccountant_0-1675266614922.png

 

 @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
        )
)

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

@BaldAccountant 

Any possibility you can share a sample file?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.