Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Team,
I have four colums Source Id and its State and Target ID and its State. I would like to achieve rollup of targetID state and then color code the source Id (Title) to either Red or Green, Soure Id can have single/multiple Target ID's associated (Parent an child tasks)
Source State (Phase 1 - Identity, Phase 2 - Investigate, Phase 3 - Prototype)
Target State ( New,ready,Done,Test,Removed,Blocked,Dev,Test)
Here are the cross check for each stage
Phase 1 - Identity
Blocked
New
Removed
Phase 2 - Investigate
Blocked
Ready
Removed
Phase 3 - Prototype
Done
Dev
Test
Blocked
Removed
Not sure how to do a rollup of status and color code. so ,any thoughts or DAX examples will help me a lot !
Solved! Go to Solution.
Hi @Anonymous ,
Try to create a measure and set conditional formatting like so:
Measure =
VAR SourceStates_ =
VALUES ( 'Stage State'[Source State] )
VAR CurSourceState_ =
MAX ( Source[Source State] )
VAR TargetStates_ =
CALCULATETABLE (
VALUES ( 'Stage State'[Target State] ),
'Stage State'[Source State] = CurSourceState_
)
VAR CurTargetStates_ =
CALCULATETABLE (
VALUES ( Source[Target State] ),
ALLEXCEPT ( Source, Source[Source ID], Source[Source State] )
)
VAR ExceptTargetStatesCount_ =
COUNTROWS ( EXCEPT ( CurTargetStates_, TargetStates_ ) )
RETURN
IF ( CurSourceState_ IN SourceStates_ && ExceptTargetStatesCount_ = 0, "Green", "Red" )
And Matrix visual can't set conditional formatting on fields on Rows or Columns.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
I don't quite understand your requirements. Could you share me more details? For example,
1. You said "Conditional Formatting by comparing Two State Columns" in title, and then how do you compare them? What's the rule?
2. Is the sample table you provided the expected result you want? How do you identify which is red or which is green?
Best Regards,
Icey
Hi Icey,
when a Source ID state is marked as Phase 1 - Identity then child tasks (Target ID) states can be in Blocked or New or Removed. If any of the child tasks are not in these three states then i want to mark Source ID as red. To set the Source ID to either Red/Green here are the cross checks
Phase 1 - Identity
Blocked
New
Removed
Phase 2 - Investigate
Blocked
Ready
Removed
Phase 3 - Prototype
Done
Dev
Test
Blocked
Removed
Hi @Anonymous ,
Try to create a measure and set conditional formatting like so:
Measure =
VAR SourceStates_ =
VALUES ( 'Stage State'[Source State] )
VAR CurSourceState_ =
MAX ( Source[Source State] )
VAR TargetStates_ =
CALCULATETABLE (
VALUES ( 'Stage State'[Target State] ),
'Stage State'[Source State] = CurSourceState_
)
VAR CurTargetStates_ =
CALCULATETABLE (
VALUES ( Source[Target State] ),
ALLEXCEPT ( Source, Source[Source ID], Source[Source State] )
)
VAR ExceptTargetStatesCount_ =
COUNTROWS ( EXCEPT ( CurTargetStates_, TargetStates_ ) )
RETURN
IF ( CurSourceState_ IN SourceStates_ && ExceptTargetStatesCount_ = 0, "Green", "Red" )
And Matrix visual can't set conditional formatting on fields on Rows or Columns.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much. This will be a game changer for me 😀
Example:
Source ID | Source State | Target ID | Target State |
219203(Red) | New | 249831 | Blocked |
247611 | Ready | ||
295531(Green) | Phase 3 - Prototype | 569794 | Done |
295531 | 328384 | Removed | |
295531 | 328382 | Done | |
295531 | 812856 | Done | |
295531 | 519393 | Dev | |
308468(Red) | Phase 3 - Prototype | 890933 | Ready |
872697 | New | ||
335460(Red) | Phase 3 - Prototype | 291654 | Done |
336072 | Removed | ||
350896 | Removed | ||
863825 | New | ||
868718 | New | ||
860056 | New |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |