Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working in project to calculate a score of each company of a same group.
To calculate the score , each company has to provide x kpi each month ( x is a number of kpi that the company have to provide each year , so x change by year) , so companies provide y kpi ( y the number of kpi that each company provides each month which is equal or less to x) .
I am using stacked colum chart to show the score by company and i am using a slicer of date ( by month) , i want to use data color function to show it the company provides all the kpi or not .
Exemple : if the company provides all the kpi , the color is blue , else the color is grey. But the number of kpi to provide change each year. ( in 2021 , the number was 10, in 2022, the number is 12).
Solved! Go to Solution.
According to your description, I create a sample.
Here's my solution.
1. Create a KPI table, don't make relationship between the two tables.
2. Create a measure.
Color =
IF (
MAX ( 'Table'[Score] )
>= MAXX (
FILTER (
ALL ( 'KPI BY YEAR' ),
'KPI BY YEAR'[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
),
'KPI BY YEAR'[KPI]
),
"Blue",
"Grey"
)
3. Select the stacked colum chart, in the Format pane, select Columns>Colors>fx, select the color measure in the dialog.
Get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.
According to your description, I create a sample.
Here's my solution.
1. Create a KPI table, don't make relationship between the two tables.
2. Create a measure.
Color =
IF (
MAX ( 'Table'[Score] )
>= MAXX (
FILTER (
ALL ( 'KPI BY YEAR' ),
'KPI BY YEAR'[Year] = YEAR ( SELECTEDVALUE ( 'Table'[Date] ) )
),
'KPI BY YEAR'[KPI]
),
"Blue",
"Grey"
)
3. Select the stacked colum chart, in the Format pane, select Columns>Colors>fx, select the color measure in the dialog.
Get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.
@OmarSek So, seems like you would use an Enter Data query to create a table with the number of KPI's by year. Then you could create a measure that compared the number of entries (COUNTROWS perhaps) in a given year, compare that to the KPI table value and return 1 or 0 so that you could then use that measure in your conditional formatting.
I like the idea, i will try to implement it and i will let you know the result.
I create a table called KPI BY YEAR , with two columns YEAR and Num KPI , this table has a relationship with just Date table .
I have a slicer of month, i am trying just to find the NUM KPI using dax formula but it doesn't function.
This is my dax forumula :
NumPKIYEAR = VALUES ('KPI BY YEAR'[Num KPI])
but i get an error.
@OmarSek , if you use legend or two measure you can not use conditional formatting. So stacked you might be using legend, so I doubt that.
Usually, color measure does a good job in that case
How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4