Get the Max vaule for every Row in Matrix Power BI

[b244293]

I have created the table(Dummy) mentioned below,

LeadId	DateName	Quarter
1	Sunday	Q1
2	Sunday	Q1
3	Sunday	Q1
4	Sunday	Q1
5	Sunday	Q2
6	Sunday	Q2
7	Sunday	Q3
8	Monday	Q1
9	Monday	Q1
10	Monday	Q2
11	Monday	Q2
12	Monday	Q2
13	Monday	Q2
14	Monday	Q2
15	Monday	Q3
16	Monday	Q3

Now, I want to display the data "How many leads are there for date name and quarters?". I have created the Matrix report like the below, (The value is the count for the leadid)

DateName	Q1	Q2	Q3
Monday	2	5	2
Sunday	4	2	1

Now, I want to highlight Max value for every row. like below,

DateName	Q1	Q2	Q3
Monday	2	5	2
Sunday	4	2	1

I have tried to find the large value for each row like the below DAX, But I could not achieve it.

measureMaxCount =

VAR mymax =

CALCULATE ( MAXX(Dummy,COUNT(Dummy[LeadId])), ALL ( Dummy[Quarter] ) )

RETURN

mymax

Kindly help me?

[dm-p]

Hi @b244293, and welcome aboard!

Just as a heads-up, you can get much faster help with DAX or data-related questions if you post them in the Desktop or DAX Commands and Tips forums. A lot more people frequent those forums rather than this one (which is concerned with extending Power BI using code), and you'll get help much more quickly than in here.

You can solve your challenge as follows:

measureMaxCount = 
    VAR Counts = SUMMARIZE(
        Dummy,
        Dummy[Quarter],
        "# Leads", COUNTROWS(Dummy)
    )
    RETURN MAXX(
        Counts,
        [# Leads]
    )

As your matrix is looking at at DateName, this will be applied as a filter going into your measure. So, we first, group the results by Quarter using a SUMMARIZE function and count the number of rows (calling this column "# Leads").

For Monday, this would look as follows at this point of the expression:

Quarter	# Leads
Q1	2
Q2	5
Q3	2

Now we have evaluated this, we can use it in a MAXX function (similar to how you were approaching the challenge initially) to get the highest value of our VAR.

This will run for each value of DateName in your matrix and return the following in Power BI:

One thing to bear in mind: you haven't specified how you would like totals to be handled, or if you are using them.

Because totals don't have a value for DateName, and look over all the data, you will get 7 here. Because this is only looking at Quarter in the VAR declaration, this is taking the largest value, as follows:

If you want the MAX of the returned values, we can modify the expression to include DateName as follows:

measureMaxCount = 
    VAR Counts = SUMMARIZE(
        Dummy,
        Dummy[DateName],
        Dummy[Quarter],
        "# Leads", COUNTROWS(Dummy)
    )
    RETURN MAXX(
        Counts,
        [# Leads]
    )

This will ensure that we also group by DateName, in case it doesn't exist in our row context (like for Totals, or another visual that doesn't have it).

The results will now look as follows:

This has taken the higest value of all unique values of DateName in the table, e.g.:

Hopefully this should be all you need. Good luck!

Daniel

[b244293]

Thank you, Super User.  I need the result like below. My goal is to highlight the biggest value of each row in conditional formating. 

Please help me.

Thanks,

Bala.

[dm-p]

My apologies - I misunderstood your requirements. You can't currently conditionally format based on a dynamic measure value, but you can do this with a measure that behaves as a flag because we can predict its value. We'll set this up similar to my previous answer, but there will be a couple of changes.

1. Common Measure

We'll need to use the # Leads a few times, so we'll create a proper measure for it so that our business logic is centralised:

# Leads = COUNTROWS(Dummy)

The results shouldn't be too surprising:

2. Max of # Leads by DateName

We'll now re-work that previous measure to calculate as before, but we need to ensure that we ignore the DateName when we calculate the max, and use our # Leads measure for consistency:

Max of # Leads by DateName = 
    VAR LeadsByQuarter = SUMMARIZE(
        ALLEXCEPT(Dummy, Dummy[DateName]),
        Dummy[Quarter],
        "# Leads", [# Leads]
    )
    RETURN MAXX(
        LeadsByQuarter,
        [# Leads]
    )

This gets us to where we were before, but the key difference is that the ALLEXCEPT will ensure that the same result is used across all values of Quarter:

3. Add 'Flag' Measure

We can't use TRUE/FALSE in conditional formatting, or something like [max value], so we can create a measure that behaves as a 0/1 'flag' and we can apply conditional formatting by that later on:

? Is Count Max Count = 
    VAR CountLeads = [# Leads]
    VAR MaxCount = [Max of # Leads by DateName]
    RETURN
        SWITCH(
            TRUE(),
            CountLeads = MaxCount, 1,
            0
        )

We can then test this by adding to our matrix and having a look. This is 1 for the highest value in each DateName row:

Result

Now we know our measures are correct, we can set up the matrix we want, e.g.:

I'll apply conditional formatting to # Leads - I'm using Background color, as per your mockup. We'll use the value of our 'flag' measure to colour the cell, e.g.:

This will now colour the background of our count as follows, e.g.:

Hopefully I've got this correct for you now - I've also attached a copy of my workbook for you to have a look at in further detail if needs be.

Regards,

Daniel

[b244293 · Accepted solution]

Excellent. It is working as expected. Thank you.