Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Need to identify data whether live or not by child ,
Raw Data:
Output:
If live = yes,"Green"
IF live = no,"Red"
If live is combination of yes && NO then it's Orange".
Could you help me here dax to get 3 colors on coonditinal formating.
Note: slicer : date range from date dimension.
Solved! Go to Solution.
Hi @VikramAdi,
You can create one custom column “Live Data” with below DAX expression:
Live Data = IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="no")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)) , "No",IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="yes")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)),"Yes","Partial"))
And apply conditional formatting for this column with Live Colour column as explained previously which will result as shown in below screenshot.
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
@SamInogic When i add live data column in values filed it's picking First or last value. brand wise giving correct but when i'm looking at parent level, giving wrong output.
Also how did you mange without appear value for parent.
Thanks @SamInogic it's working..
Having one doubt here, same concept how can we apply to the Matrix report ?
Hi @VikramAdi ,
Did the Matrix Report means the Matrix Visual in Power BI Report? If yes then you can add the Matrix Visual with below configuration and same output will be there.
Let us know if you are expecting something different from this.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Currently acheved upto below screen.
But i want change font
Hi @VikramAdi,
You can create one custom column “Live Data” with below DAX expression:
Live Data = IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="no")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)) , "No",IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="yes")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)),"Yes","Partial"))
And apply conditional formatting for this column with Live Colour column as explained previously which will result as shown in below screenshot.
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Remove the Live column from the table and place the following measure
New Live =
IF (
HASONEVALUE ( 'Table'[Live] ),
VALUES ( 'Table'[Live] ),
"Partial"
)
then you can simply format the color based on the value
@tamerj1 Yes, created a measure same. Yes and No (both) combination not working on above scenarios
Seems I'm misunderstanding or missing something. Would you please paste some screenshots of what you have done and achieve so far to clarify the picture?
@tamerj1 your formula not working for both yes and No cases.
@SamInogic your approach is fine but unable to get like below.
Hi @VikramAdi
please try
FormatMeasure =
IF (
HASONEVALUE ( 'Table'[Live] ),
IF ( VALUES ( 'Table'[Live] ) = "Yes", "Green", "Red" ),
"Orange"
)
Thanks for your responce @SamInogic , It's working fine. But Orange color font i want display as "Partial".
In live column, orange background coming like Yes, No, But required to display as "Partial".
Hi @VikramAdi ,
You can create a new column in your table for Live Colour with below DAX expression:
Live Colour = IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="no")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)) , "Red",IF(CALCULATE(COUNTROWS('Table'), FILTER(FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
), 'Table'[Live] ="yes")) = CALCULATE(COUNTROWS('Table'), FILTER (
'Table',
'Table'[Parent] = EARLIER ('Table'[Parent])
&& 'Table'[Child] = EARLIER ( 'Table'[Child] )
)),"Green","Orange"))
Please refer to the below screenshot for the same.
Now we have a colour based on Yes, No Live Values and will apply those colours to background colur for Live column so select Conditional formatting>> Background Colour option as shown in the below screenshot.
This will allow you to select column based on which the colour will be applied. Please refer to the below screenshot for the same.
Below will be the result you will see with background colour for Live Column,
If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |