cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BI_Analyticz
Helper V
Helper V

Pick up first non null value if the current value is null or some defined value

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.

 

ComputerNameMonthStatus
Comp A01-Nov-21null
Comp A01-Dec-21null
Comp A01-Jan-22Compliant
Comp A01-Feb-22Compliant
Comp A01-Mar-22Non-Compliant
Comp B01-Nov-21Compliant
Comp B01-Dec-21null
Comp B01-Jan-22Non-Compliant
Comp B01-Feb-22Compliant
Comp B01-Mar-22Non-Compliant
1 ACCEPTED 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]
    )

vkkfmsft_0-1648792144851.png

 

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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]
    )

vkkfmsft_0-1648792144851.png

 

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.