March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
Excellent. It is working as expected. Thank you.
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
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
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.
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
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Excellent. It is working as expected. Thank you.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
9 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
11 | |
3 | |
2 | |
2 | |
2 |