Search code examples
powerbidaxdashboardm

filter based on all characteristics/columns of a person/row in Power BI desktop

I am relatively new to Power BI and have been asked to create a dashboard that displays report card like information and updates by selecting only the individual's name. I have performance measures that compare individual to state and overall performance using ALLEXCEPT and filters and I got it to work by selecting the filters individually for that person but I am having difficulties figuring out how to make it work without selecting the additional filters.

The demographics are in both the 'provider demo' and 'sql measures table' listed for the individual that I am selecting.

Below is the relationships I built: relationships

The measures that I want to automatically work without additional filter selecting are below and currently function how I want them to besides the need to additionally select filters:

Overall Reporting Rate =
CALCULATE (
    DIVIDE (
        SUM ( 'sql measures table'[Reporting Numerator] ),
        SUM ( 'sql measures table'[Reporting Denominator] ),
        0
    ),
    ALLEXCEPT ( 'sql measures table', 'sql measures table'[PROC_DAY_DATE (bins) 3] )
)

--

State Performing Rate =
CALCULATE (
    DIVIDE (
        SUM ( 'sql measures table'[Performing Numerator] ),
        SUM ( 'sql measures table'[Performing Denominator] ),
        0
    ),
    ALLEXCEPT (
        'sql measures table',
        'sql measures table'[PROC_DAY_DATE (bins) 3],
        'sql measures table'[PRV_STATE],
        'sql measures table'[measure]
    )
)

I've tried having the ALLEXCEPT statement include the columns from the demographics table.

Is there a way through DAX or M to be able to only select the person's name and have the measures work appropriately?

I am producing a dashboard that displays a matrix with numerators, denominators, and measures like the ones above as well as bar graphs that display measures of individual performance, state performance, and nationwide performance of the network. The goal is to only have to select one filter- the person's name and have the dashboard fully update. If I add a filter for the state from the sql table that contains all the metrics data, the matrix updates appropriately. Although I have relationships set up, I tried selecting the state from the demographics table and it didn't work correctly, as the performance rates for the state and nationwide were the same.

dashboard example

Above is a mock-up of the dashboard. The graph on the side is the performance for the individual, state, and nation for the first measure. There will be a graph for each measure going down the matrix. In order to get them to work properly, I selected the state as a visual-level filter.

Each provider is assigned certain measured, defined in a table that has a relationship established. I have to select a specific filter from the sql fact table in order to get the correct measures to display for the provider, and my goal is to be able to select the provider and have the measures assigned to that person automatically display.

Solution

  • Let me re-state your problem to make sure I understood you correctly.

    You want to calculate 3 types of ratios:

    1. Ratio for the select person
    2. Ratio for the entire state where this person is located
    3. Ratio for the entire country(all states?)

    and then you want to compare these 3 ratios. Further, such ratios should remain responsive to the filters/slicers from other dimensions.

    If this statement is incorrect, please add more detailed description of what you are trying to build. If it's correct, here is how I would approach it:

    First, design basic measures for the nominator and denominator so that you don't have to write them multiple times (I will be liberal with the names):

    Nominator = SUM('sql measures table'[Performing Numerator])

Denominator = SUM('sql measures table'[Performing Denominator])

Ratio = DIVIDE([Nominator], [Denominator], 0)

    If you select a person's name on a slicer, [Ratio] will be correctly calculated for the selected person.

    Next, we need to build a ratio that shows the same performance for the entire state of the selected person:

    Statewide Ratio = CALCULATE( [Ratio], ALL(Person), VALUES(Person[State]))

    where Person is the name of the table with your persons ('provider demo'?)

    So, if you select a person who lives in CA, Statewide Ratio will calculate performance ratio for all persons in CA.

    Finally, design a ratio that remains the same regardless of the selected person (and thus shows the entire system performance):

    Nationwide Ratio = CALCULATE([Ratio], ALL(Person))

    Such ratio will be insensitive to any selection from table "Person", but will respond to the filters from other dimentions. If you want it to be insensitive to anything except Measure dimension, then your existing formula will work:

        Systemwide Ratio =
CALCULATE (
    [Ratio],
    ALLEXCEPT ( 'sql measures table', 'sql measures table'[PROC_DAY_DATE (bins) 3] )
)

    If you create a chart with all these 3 ratios, and select a Person from a slicer or whatever, the chart will compare this person's performance vs his/her state performance vs overall system performance. I assume that's what you wanted (?)

    Side Note: I would avoid using "Visual level filters" - they are confusing and are not part of the data model. All filtering can be done either through dashboard visuals or DAX.