topic Re: Can you apply different colours to one row? in Desktop

Can you apply different colours to one row?

Stelz — Thu, 31 Oct 2024 04:06:37 GMT

Hi!
I have created an annual leave regsiter using a matrix visual where employees are grouped by job description. Im using a measure to colour in my values based on the length of the leave and the status of the approval (i.e approved, pending, rejected). It all appears to work except in the instance where an employee has multiple leave submissions that have differing approval statuses.

In the selected row, this person has an approved leave and a pending leave, both are coloured Green. It appears the expression I am using is merely prioritising a colour

This is the expression im using to conditionally format my background/font colours.

Colour = VAR Leave = [Leave count]
RETURN 
SWITCH(
    TRUE(),
    Leave = 0, "white",
    Leave > 0 && COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Approved")) > 0, "#168E6A",
    Leave > 0 && COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Pending")) > 0, "#f6e828",
    Leave > 0 && COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Rejected")) > 0, "#f63d28",
    "white"
)

Am i missing something, or does Power BI simply have limits in applying different colors within the same row?

Thank you

Re: Can you apply different colours to one row?

FreemanZ — Thu, 31 Oct 2024 04:54:37 GMT

hi Stelz ,

It seems the date context is not working here.

How are the date table and the leave register table related?

Re: Can you apply different colours to one row?

danextian — Thu, 31 Oct 2024 07:10:29 GMT

Hi Stelz

AFAIK, the limit is what your device's memory can handle. I would add Leave count and the COUNTROWS calculations as separate measures to a matrix to check if they return the expected values.

Re: Can you apply different colours to one row?

KNP — Thu, 31 Oct 2024 17:18:38 GMT

Hi Stelz ,

SWITCH statements are processed in order, so if you change the order you should be able to acheive what you want.

Re: Can you apply different colours to one row?

Stelz — Thu, 31 Oct 2024 21:34:46 GMT

Hi KNP

Maybe using Switch isnt the right method, as not one approval status has a priority other the other. I want to be able to see each leave in the relevant colour.

Re: Can you apply different colours to one row?

KNP — Thu, 31 Oct 2024 21:44:17 GMT

I see what you're saying.

I think I'd need to see some sample data to better understand, and as FreemanZ mentioned, detail about the mode/relationships.

What defines uniqueness in the Leave Register table?

Re: Can you apply different colours to one row?

Stelz — Thu, 31 Oct 2024 21:48:12 GMT

Hello FreemanZ

Thanks for your response

This is the expression used to calculate the leave count

Leave count = SUMX ( SUMMARIZE ('Leave Register', 'Leave Register'[EmployeeName], 'Leave Register'[Leave Start], "+1" , COUNTX ( FILTER ('Date', 'Date'[Date] >= MIN ('Leave Register'[Leave Start]) && 'Date'[Date] <= MAX ('Leave Register'[Leave end])), CALCULATE ( COUNT ('Leave Register'[EmployeeName])))),[+1]+ 0 )

this is how the visual looks without the colour formatting applied

Re: Can you apply different colours to one row?

Stelz — Thu, 31 Oct 2024 21:56:13 GMT

I understand not all values will be visible because im working over a timeline, but i only need the current date + 2-3 months in advance. I plan to filter out values older than today.

Measures added to a seperate matrix are giving the correct values

Re: Can you apply different colours to one row?

Stelz — Thu, 31 Oct 2024 22:26:57 GMT

I am totally new to all this, and I was pinpointing the [colour] measure as the issue since the rest of the data appears to be working well, but there very may well be other issues.

The process is as follows

- Employee applies for leave using a digital form

- Leave request is sent for approval to the relevant person

- Data is simultaneously sent to SharePoint list, which is synced to the PowerBI Matrix (the 'Leave Register')

- The Approval status is defaulted to pending until later updated to approved or rejected

- I calculate the 'Leave Count' as per my response to FreemanZ, which gives me a value of 1 in the relevant day/month/year.

Sorry if this is too much information! Is there any partucliar dataset youd like to see?

Re: Can you apply different colours to one row?

danextian — Thu, 31 Oct 2024 22:39:13 GMT

What I meant was you need to set it up the same way you did with your current matrix visual. There will be more measures in there in total. That is for you to check if they return the expected values.

Re: Can you apply different colours to one row?

KNP — Fri, 01 Nov 2024 01:04:26 GMT

It's all useful info, a screenshot of your data model showing the relationships would help.

I think, if I understand correctly, the approach I would take is slightly different (and may not work 😄)

If your Leave Status was something like this...

Leave Status = VAR ApprovedCount = COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Approved")) VAR PendingCount = COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Pending")) VAR RejectedCount = COUNTROWS(FILTER('Leave Register', 'Leave Register'[Approved] = "Rejected")) RETURN SWITCH( TRUE(), ApprovedCount > 0, 1, PendingCount > 0, 2, RejectedCount > 0, 3, 0 )

And your colour measure something like this...

Colour = VAR LeaveStatus = [Leave Status] RETURN SWITCH( LeaveStatus, 0, "rgba(0,0,0,0)", 1, "#168E6A", // Approved 2, "#f6e828", // Pending 3, "#f63d28", // Rejected "rgba(0,0,0,0)" )

There's a chance this could simplify it.

The code is untested.