Can you apply different colours to one row?

[Stelz]

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

[KNP]

Hi @Stelz ,

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

[Stelz]

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. 

[KNP]

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?

[Stelz]

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? 

[KNP]

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.

[danextian]

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.

[Stelz]

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

[danextian]

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.

[FreemanZ]

hi @Stelz ,

It seems the date context is not working here. 

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

[Stelz]

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