DAX Measure help - select a value in the column

[YvonneTian]

Hi BI Gurus,

I have a question to write a measure based on a series of selected values including both filters and measures.

I have this table of data and I want to show the table visually. To do that, I need to show my top 5 Industry Meta Driver ranking by DriverQ0.25.

I have managed to achieve this using the following 2 measures (The example only shows the 1st mapping MD, I will tweak it 5 times to show 1st-5th mapping MD)

rank = rankx(

allselected('Driver quartile function'[Industry Meta-Driver]),

calculate(sum('Driver quartile function'[DriverQ0.25])),

,

desc,Dense)

1st mapping MD = "1. " & CALCULATE(

Max('Driver quartile function'[Industry Meta-Driver]),

filter(allselected ('Driver quartile function'[Industry Meta-Driver]),[rank] = 1) )

Then I need to find the corresponding Q0.25, Q0.50 and Q0.75 for this 1st mapping MD. E.g. for "Data Quality and timeliness issues", the measures should show 7.5%, 11.7% and 16.2%. Any Idea on how to achieve this? Thank you so much!!

**I have tried to use a tweaked version of 1st mapping MD measure, but it did not work well because the corresponding Q0.50/Q0.75 may not follow the ranking for Q0.25. E.g. for the 5th mapping MD"Manual work management", it has lowest Q0.25, but 2nd in Q0.5

[amitchandak]

@YvonneTian , I think you need percentile calculation

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459

[YvonneTian]

Um Thanks Amitchandak!

My question however is not related to the percentile... The question I had is more related to identifying the corresponding value in the table. Let me try to use another example to explain my question:

I have a table looks like this, the rank is defined by DriverQ0.25 from largest to smallest:

Now to present this data, I need to make a dashboard with cards looking like this:

The ideal result would look like this:

I have tried to use the combination of MAX and CALCULATE to achieve the result and not getting it right... I hope the explaination gets a bit better this time!! 

Many thanks for responding to my quesiton 🙂

[Anonymous · Accepted solution]

Hi @YvonneTian ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create the measures as below to achieve it:

1. Get the Meta-Driver which rank is 1 and 2 and put the measure [Rank1DriverQ0.25] and [Rank2DriverQ0.25] on the card visual

Measure1 = 
VAR _rank = [rank]
RETURN
    MAXX (
        FILTER ( 'Driver quartile function', _rank = 1 ),
        [Industry Meta-Driver]
    )

Rank1DriverQ0.25 = 
"1. "
    & MAXX (
        GROUPBY (
            ALLSELECTED ( 'Driver quartile function' ),
            'Driver quartile function'[Industry],
            'Driver quartile function'[Industry Meta-Driver],
            'Driver quartile function'[Function]
        ),
        [Measure1]
    )

Measure2 = 
VAR _rank = [rank]
RETURN
    MAXX (
        FILTER ( 'Driver quartile function', _rank = 2 ),
        [Industry Meta-Driver]
    )

Rank2DriverQ0.25 = 
"2. "
    & MAXX (
        GROUPBY (
            ALLSELECTED ( 'Driver quartile function' ),
            'Driver quartile function'[Industry],
            'Driver quartile function'[Industry Meta-Driver],
            'Driver quartile function'[Function]
        ),
        [Measure2]
    )

2. Create a measure as below to determine which values should display

Flag1 = 
IF (
    SELECTEDVALUE ( 'Driver quartile function'[Industry Meta-Driver] )
        = RIGHT ( [Rank1DriverQ0.25], LEN ( [Rank1DriverQ0.25] ) - 3 ),
    1,
    0
)

Flag2 = 
IF (
    SELECTEDVALUE ( 'Driver quartile function'[Industry Meta-Driver] )
        = RIGHT ( [Rank2DriverQ0.25], LEN ( [Rank2DriverQ0.25] ) - 3 ),
    1,
    0
)

3. Create two table visual and apply a visual-level filter with the condition(Flag1 is 1, Flag2 is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[YvonneTian]

Hi Rena,

This is a wonderful solution!! Thank you so much for putting in the effort to pull it through!! I will upload a simplified BI file next time to help with the answer.

I have asked the same question around and managed to get the results using a dirty LOOKUPVALUE function. It kinda does the job but requires 3 measures for each driver I selected. Here is my workaround

1st mapping Q0.25 = LOOKUPVALUE('Driver quartile function'[DriverQ0.25],
'Driver quartile function'[Industry Meta-Driver], 
'Driver quartile function'[1st mapping ND],
'Driver quartile function'[Industry], VALUES('Driver quartile function'[Industry]),
'Driver quartile function'[Function], VALUES('Driver quartile function'[Function]))

1st mapping Q0.50 = LOOKUPVALUE('Driver quartile function'[DriverQ0.50],
'Driver quartile function'[Industry Meta-Driver], 
'Driver quartile function'[1st mapping ND],
'Driver quartile function'[Industry], VALUES('Driver quartile function'[Industry]),
'Driver quartile function'[Function], VALUES('Driver quartile function'[Function]))

1st mapping Q0.75 = LOOKUPVALUE('Driver quartile function'[DriverQ0.75],
'Driver quartile function'[Industry Meta-Driver], 
'Driver quartile function'[1st mapping ND],
'Driver quartile function'[Industry], VALUES('Driver quartile function'[Industry]),
'Driver quartile function'[Function], VALUES('Driver quartile function'[Function]))