Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soft5
Frequent Visitor

Count occurences with status change A to B for category

Dear Community,

Unfortunately I am lacking DAX fluency to fix the below formula and would like to ask for help.

The task is to count records of invoices that changed status from A to B.

 

My data sample:

CompanyINVOICE_IDStatusA/BDate_2Expected values
X3681openA4/7/2023same
X3681in progressA4/11/2023 
D47163in progressA4/7/2023same
D47163failedA4/11/2023 
Y70050failedB4/6/2023same
Y70050failedB4/7/2023 
X79065in progressA4/6/20231
X79065in progressB4/7/20230
X79065in progressA4/11/2023 
X80856openB4/6/20230
X80856openA4/7/2023same
X80856failedA4/11/2023 
D1a040failedA4/7/2023same
D1a040openA4/11/2023 
X22ac6openA4/6/20231
X22ac6openB4/7/2023 
X3c3dcfailedA4/7/2023same
X3c3dcopenA4/11/2023 
Y467ccopenA4/7/2023same
Y467ccin progressA4/11/2023 
Y4987aopenA4/7/2023same
Y4987afailedA4/11/2023 
C5eefdin progressA4/11/2023same
X5eefdopenA4/11/2023 
X83ab5in progressA4/7/20231
X83ab5failedB4/11/2023 
C9c40eopenA4/7/2023same
C9c40ein progressA4/11/2023 
CbcaeffailedB4/11/2023 
Ycba98failedA4/6/2023same
Ycba98failedA4/7/2023same
Ycba98in progressA4/11/2023 
Xd7b17failedA4/7/2023same
Xd7b17openA4/11/2023 
Ce0ad7in progressB4/7/2023 
Ye42f6in progressA4/6/20231
Ye42f6in progressB4/7/20230
Ye42f6openA4/11/2023 
Df7a73failedB4/11/2023 

I want to achieve the logic that is in column Expected values and then to count only "1"

like in excel formula =IF(B4=B5,IF(AND(D4="A",D5="B"), "1",IF(AND(D4="B",D5="A"),"0","same")),"")

 

 

I started this way ( no success;() :

StatusChangedA to B = var _cur_date = MAX(‘Table1’[Date_2])

var _pre_date = MAXX( FILTER( ALL(‘Table1’)  , ‘Table1’[Date_2]<_cur_date) ,[Date_2])

var _cur_list  = FILTER( ALL(‘Table1’) , ‘Table1’[Date_2] = _cur_date)

var _pre_list =  FILTER( ALL(‘Table1’) , ‘Table1’[Date_2] = _pre_date)

var _prevstat = LOOKUPVALUE(Table1[A/B],Table1[INVOICE_ID],_pre_list)

var _curstat =LOOKUPVALUE(Table1[A/B],Table1[INVOICE_ID],_cur_list)

var _t= INTERSECT(_pre_list,_cur_list)

return

COUNTROWS( FILTER(_t, AND (_prevstat= "A",_curstat="B")))

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_0-1685711669066.png

 

 

Expected values CC =
VAR _currentrow = Data[A/B]
VAR _nextrowdate =
    MINX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] > EARLIER ( Data[Date_2] )
        ),
        Data[Date_2]
    )
VAR _nextrow =
    MAXX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] = _nextrowdate
        ),
        Data[A/B]
    )
RETURN
    SWITCH (
        TRUE (),
        _currentrow = "A"
            && _nextrow = "B", "1",
        _currentrow = "B"
            && _nextrow = "A", "0",
        _currentrow = _nextrow, "Same",
        _nextrow = BLANK (), " "
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

 

Jihwan_Kim_0-1685711669066.png

 

 

Expected values CC =
VAR _currentrow = Data[A/B]
VAR _nextrowdate =
    MINX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] > EARLIER ( Data[Date_2] )
        ),
        Data[Date_2]
    )
VAR _nextrow =
    MAXX (
        FILTER (
            Data,
            Data[INVOICE_ID] = EARLIER ( Data[INVOICE_ID] )
                && Data[Date_2] = _nextrowdate
        ),
        Data[A/B]
    )
RETURN
    SWITCH (
        TRUE (),
        _currentrow = "A"
            && _nextrow = "B", "1",
        _currentrow = "B"
            && _nextrow = "A", "0",
        _currentrow = _nextrow, "Same",
        _nextrow = BLANK (), " "
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you so much for help. It works fine.:)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors