Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Measure help - select a value in the column

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        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