Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm currently building a Matrix with Count of Open and Closed Tickets for a Manager in a Help Desk. Looking to highlight the top 1 value and lowest value in each column for a easier visual to see the top and lowest performers.
I created the following 2 measures in DAX and the top value is showing up formatted with the green background color, but the false statement on the IF statement will not format the lowest value. Any help would be much appreciated.
Measure #1:
Count of Opened Tickets = COUNTA('Opened By Tickets'[Ticket])
Measure#2
VAR RankingContext = VALUES(Contacts[Full_name])
VAR TopNumber = 1
RETURN
IF(
CALCULATE(
[Count of Opened Tickets],
TOPN(TopNumber,ALL(Contacts[Full_name]),[Count of Opened Tickets],DESC),RankingContext),"#5ff442",IF(CALCULATE([Count of Opened Tickets],
TOPN(TopNumber,ALL(Contacts[Full_name]),[Count of Opened Tickets],ASC),RankingContext),"#ff0000"))
Added a view of how the data looks in the Visual:
Solved! Go to Solution.
Hi @tayriley88
Since columns/measures added in the "Value" field of the matrix are sperate, you need to format color for each column/measure.
For example,to format for Opened Tickets,
First create measures for Opened Tickets,
opened_tickets = COUNT('opened by tickets'[tickets]) rank_open = RANKX(ALL(contacts[full_name]),[opened_tickets],,DESC,Dense) colored_opened = VAR top_open = MINX ( ALL ( contacts[full_name] ), [rank_open] ) VAR low_open = MAXX ( ALL ( contacts[full_name] ), [rank_open] ) RETURN SWITCH ( [rank_open], top_open, 1, low_open, 0 )
Next, add conditional formatting for the "opened_tickets" field,
See how to add "Conditional formatting in tables/matrix"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tayriley88
Since columns/measures added in the "Value" field of the matrix are sperate, you need to format color for each column/measure.
For example,to format for Opened Tickets,
First create measures for Opened Tickets,
opened_tickets = COUNT('opened by tickets'[tickets]) rank_open = RANKX(ALL(contacts[full_name]),[opened_tickets],,DESC,Dense) colored_opened = VAR top_open = MINX ( ALL ( contacts[full_name] ), [rank_open] ) VAR low_open = MAXX ( ALL ( contacts[full_name] ), [rank_open] ) RETURN SWITCH ( [rank_open], top_open, 1, low_open, 0 )
Next, add conditional formatting for the "opened_tickets" field,
See how to add "Conditional formatting in tables/matrix"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Awesome thank you very much!
Hi @tayriley88
It may be easy to format with "conditional formatting" feature in Matrix as this article introduced.
You need to create a measure to perform this.
Before creating correct measures, please clear me:
How do "Contacts" table and "Opened By Tickets" relate?
It seems you add [Full_name] from "Contacts" table and [Count of Opened Tickets] from "Opened By Tickets" table in the matrix visual.
Could you provide a screenshot of your matrix and which columns added into the "column"/"row"/"value" fields?
Best Regards
Maggie
Here is how the Rows and Values are added
Here is my relationship map. Contacts Table and Opened By Tickets are linked by opened by from Opened By Tickets and Full_name on the Contacts Table.
In a matrix visual how to achieve this ?
I have this measure,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |