Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dataset like below.
I want to check if status is null then pick up the first non-null value for Comp A which is Compliant. 2nd condition - if the value is Non-Compliant then check the value for next month and use that value. Example Comp B's Jan 2022 is Non-Compliant but Feb'2022 is Compliant so we need to change Jan 2022's value to Compliant.
Can this be done in DAX or M for having a new column. I dont want a measure to be created.
ComputerName | Month | Status |
Comp A | 01-Nov-21 | null |
Comp A | 01-Dec-21 | null |
Comp A | 01-Jan-22 | Compliant |
Comp A | 01-Feb-22 | Compliant |
Comp A | 01-Mar-22 | Non-Compliant |
Comp B | 01-Nov-21 | Compliant |
Comp B | 01-Dec-21 | null |
Comp B | 01-Jan-22 | Non-Compliant |
Comp B | 01-Feb-22 | Compliant |
Comp B | 01-Mar-22 | Non-Compliant |
Solved! Go to Solution.
Hi @BI_Analyticz ,
Please try the following formula:
Column =
VAR FirNonBlank =
CALCULATE (
MIN ( 'Table'[Month] ),
FILTER (
'Table',
'Table'[Status] <> BLANK ()
&& 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
)
)
RETURN
SWITCH (
[Status],
"",
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
'Table',
'Table'[Month] = FirNonBlank
&& 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
)
),
"Non-Compliant",
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
'Table',
'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
&& 'Table'[Month] = EDATE ( EARLIER ( [Month] ), 1 )
)
),
[Status]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@BI_Analyticz , a new column like
new column =
var _min = minx(filter(Table, [ComputerName] = earlier([ComputerName]) && [Month] >earlier([Month]) && not(isblank([Status])) ), [Month])
return
if(isblank([Status]) , maxx(filter(Table, [ComputerName] = earlier([ComputerName]) && [Month] =_min ), [Status]),[Status])
@amitchandak Thanks for replying. Is there a way to pickup the value of Feb 2022 which is max month -1 or n-1
Hi @BI_Analyticz ,
Please try the following formula:
Column =
VAR FirNonBlank =
CALCULATE (
MIN ( 'Table'[Month] ),
FILTER (
'Table',
'Table'[Status] <> BLANK ()
&& 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
)
)
RETURN
SWITCH (
[Status],
"",
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
'Table',
'Table'[Month] = FirNonBlank
&& 'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
)
),
"Non-Compliant",
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
'Table',
'Table'[ComputerName] = EARLIER ( 'Table'[ComputerName] )
&& 'Table'[Month] = EDATE ( EARLIER ( [Month] ), 1 )
)
),
[Status]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |