Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have few questions on DAX conditional formatting.
There is a KPI whose target is 10% for the year jan 2020 - mar 2021, now the target has been revised to 15% for from apr - 2021, how do I write a specific DAX for conditional formatting based on time period.
Currently am using the the Icon based conditional formatting for the year 2020, now since the target has changed when I override the conditional formatting based on apr 2021 target, the conditional formatting of 2020 is being changed too, which am trying to avoid.
Can someone help me on DAX where in i can use icon based conditional formatting for two different targets based on time period mentioned above
Solved! Go to Solution.
Hi @Anonymous ,
The following setting is to put the year and month together into the following format: 202001, 202002, xxx etc, and convert them into numerical values. If the month number less than 10 and you didn't make any adjustment(
VAR _selymonth =
VALUE (
CONCATENATE ( _year, IF ( _month < 10, CONCATENATE ( "0", _month ), _month ) )
)
The condition "SELECTEDVALUE ('Table'[achieved]) >= _target * 0.98 " is to judge whether the achieved value reaches 98% or more of the target value.
I updated the sample pbix file base on your provided date table, please find the attachment for the details.
If the above one is not applicable for your scenario, it is better to share your sample pbix file(exclude sensitive data) with me in order to provide you a suitable solution...
Best Regards
Hi @Anonymous ,
You can refer the content in the following links to achieve it:
Conditional Formatting based on Time for multiple conditions
Custom Conditional Formatting Techniques In Power BI
If the above one is not applicable for your scenario, please provide some sample data(exclude sensitive data) and your expected result with samples and function details. Thank you.
Best Regards
am trying to achieve above type of icon conditional formatting, since the target was changed from april 2021, am not able to amend it in my matrix table to new targets as the old targets are also reflecting per new when i try to amend it in icon conditional formatting, therefore am looking for a dax which takes the conditional formatting based on time period ( jan,feb etc)...In my scenario my target was same from jan 2020 to mar 2021, it changed from apr 2021.
i want to show icon conditional formatting in same table for two different targets
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Best Regards
Thank you for your expertise.
Just few small changes, so in my matrix table there would be no target column and there would be no month column
Month i'll select from the slicer and target is already aware by operations.
so when i select for instance march 2021, the formatting should be based on 10%, when i select april 2021 the formatting should be based on 15% in the same table. since here formatting is changing based on months how should i amend this factor in my conditional formatting
my tager was 10% from Jan 2020 to Mar 2021, from Apr 2021 it has changed to 15%
In monthly slicer whichever month i select formatting should reflect accordingly.
Please share your thoughts on this
Along with red and green there is also amber in my RAG
Hi @Anonymous ,
I updated my sample pbix file(see attachment), please check whether it can achieve your requirement.
Could you please explain that which situation it will display red, green, amber icon separately in?
Best Regards
Thank you for the update.
Green is on target, amber is 2% from target and red is >2% from target, please update
Hi @Anonymous ,
I updated the sample pbix file base on your requirement, please find it for the details.
Best Regards
Really appreciate your quick solution,Thanks for all your help.
I just have few questions in DAX , just wanted to clarify so that i can use with different combinations whenever need be
1)could you please help me und why did we take month <10 and o in selymonth variable
2) why are we multipying with 0.98?
Hi @Anonymous ,
The following setting is to put the year and month together into the following format: 202001, 202002, xxx etc, and convert them into numerical values. If the month number less than 10 and you didn't make any adjustment(
VAR _selymonth =
VALUE (
CONCATENATE ( _year, IF ( _month < 10, CONCATENATE ( "0", _month ), _month ) )
)
The condition "SELECTEDVALUE ('Table'[achieved]) >= _target * 0.98 " is to judge whether the achieved value reaches 98% or more of the target value.
I updated the sample pbix file base on your provided date table, please find the attachment for the details.
If the above one is not applicable for your scenario, it is better to share your sample pbix file(exclude sensitive data) with me in order to provide you a suitable solution...
Best Regards
Thank you for the update, i have used the below dax var measure for monthly and its working for me, am trying to amend the same for weekly too is there a way where i can use the weekly column and index column in the same variable of below, because the month and week would be in a slicer mode and kpi numbers in a matrix table.
when i select the month in slicer conditional formatting is working fine as per below variable, but when i select a week it isnt working.
I need to combine the month and week var measure in one dax beacuse the matrix table shows both the monthly and weekly in one single table, could you please tell me a way where i can amend both week and month in same variable measure in the below, so that whenever i select week/month in slicer the formatting should be aligned.
VAR _TARGET = IF(
SELECTEDVALUE('CX MONTH'[Index]) >=0 && SELECTEDVALUE('CX MONTH'[Index])<=7 ,0.10,
IF(SELECTEDVALUE('CX MONTH'[Index])>=8, 0.12, BLANK())
)
RETURN
IF(
[achieved measure] >= _TARGET,1,
IF([achieved measure]>= _TARGET*0.98, 2,3))
any update on this please, i could achieve the result partially.kindly help for complete resolution
Hi @Anonymous ,
Could you please share your sample pbix file with me in order to make troubleshooting and provide a suitable solution for your scenario? Because I don't know that how did you set the visualizations and is there any relationship be created among these tables(month table, week table and fact tables etc)...
Best Regards
i have used the below dax var measure for monthly and its working for me, am trying to amend the same for weekly too is there a way where i can use the weekly column and index column in the same variable of below, because the month and week would be in a slicer mode and kpi numbers in a matrix table.
when i select the month in slicer conditional formatting is working fine as per below variable, but when i select a week it isnt working.
I need to combine the month and week var measure in one dax beacuse the matrix table shows both the monthly and weekly in one single table, could you please tell me a way where i can amend both week and month in same variable measure in thebelow, so that whenever i select week/month in slicer the formatting should be aligned.
VAR _TARGET = IF(
SELECTEDVALUE('CX MONTH'[Index]) >=0 && SELECTEDVALUE('CX MONTH'[Index])<=7 ,0.10,
IF(SELECTEDVALUE('CX MONTH'[Index])>=8, 0.12, BLANK())
)
RETURN
IF(
[achieved measure] >= _TARGET,1,
IF([achieved measure]>= _TARGET*0.98, 2,3))
I have created a relationship for months and weekly table with the main data table, now i would like to write a dax for week and month in one single dax as the matrix table of kpi consist of both monthly and weekly data, below are the monthly and weekly tables that i created for relationship with main data table
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |