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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Siriwat_Lukin
Helper I
Helper I

Create index column and when found 0 it's will start from 1 again

Hello

 

I'm Siriwat, I try to create index column as example table below (from Excel)

 

Siriwat_Lukin_0-1658203735734.png

 

I have 1 column "Work Day" and  i try to create result column in PBI but not successful.

Could you please help me to solve or have any suggestions?

 

Thank in advance^^

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your actual data model looks like, but I tried to create a sample pbix file like below.

It is for creating a new column.

 

Please check the below Calculated Column and attached pbix file.

 

Result CC =
VAR _daytable =
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 1, 31 ) ),
        "@daynumber", DAY ( [Date] )
    )
VAR _addDATAtable =
    ADDCOLUMNS (
        _daytable,
        "@WorkDay", MAXX ( FILTER ( Data, Data[Work Day] = [@daynumber] ), Data[Work Day] )
    )
VAR _addpreviousdaynumber =
    ADDCOLUMNS (
        _addDATAtable,
        "@previousdaynumber",
            MAXX (
                FILTER ( _addDATAtable, [@daynumber] < EARLIER ( [@daynumber] ) ),
                [@daynumber]
            )
    )
VAR _addflag =
    ADDCOLUMNS (
        _addpreviousdaynumber,
        "@flag",
            IF (
                MAXX (
                    FILTER (
                        _addpreviousdaynumber,
                        [@daynumber] = EARLIER ( [@previousdaynumber] )
                    ),
                    [@WorkDay]
                )
                    = BLANK (),
                1,
                0
            )
    )
VAR _addgroupflag =
    ADDCOLUMNS (
        _addflag,
        "@group", SUMX ( FILTER ( _addflag, [@daynumber] <= EARLIER ( [@daynumber] ) ), [@flag] )
    )
VAR _addnumberflag =
    ADDCOLUMNS ( _addgroupflag, "@numberflag", IF ( [@WorkDay] <> BLANK (), 1, 0 ) )
VAR _addresult =
    ADDCOLUMNS (
        _addnumberflag,
        "@result",
            IF (
                [@WorkDay] = BLANK (),
                BLANK (),
                SUMX (
                    FILTER (
                        _addnumberflag,
                        [@group] = EARLIER ( [@group] )
                            && [@daynumber] <= EARLIER ( [@daynumber] )
                    ),
                    [@numberflag]
                )
            )
    )
RETURN
    MAXX (
        FILTER (
            SUMMARIZE ( _addresult, [@daynumber], [@WorkDay], [@result] ),
            [@WorkDay] = Data[Work Day]
        ),
        [@result]
    )

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

5 REPLIES 5
Siriwat_Lukin
Helper I
Helper I

Dear Jihwn_Kim

The answer is correct.

Thank you very much. i will learn from you formula and try to understand.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your actual data model looks like, but I tried to create a sample pbix file like below.

It is for creating a new column.

 

Please check the below Calculated Column and attached pbix file.

 

Result CC =
VAR _daytable =
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 1, 31 ) ),
        "@daynumber", DAY ( [Date] )
    )
VAR _addDATAtable =
    ADDCOLUMNS (
        _daytable,
        "@WorkDay", MAXX ( FILTER ( Data, Data[Work Day] = [@daynumber] ), Data[Work Day] )
    )
VAR _addpreviousdaynumber =
    ADDCOLUMNS (
        _addDATAtable,
        "@previousdaynumber",
            MAXX (
                FILTER ( _addDATAtable, [@daynumber] < EARLIER ( [@daynumber] ) ),
                [@daynumber]
            )
    )
VAR _addflag =
    ADDCOLUMNS (
        _addpreviousdaynumber,
        "@flag",
            IF (
                MAXX (
                    FILTER (
                        _addpreviousdaynumber,
                        [@daynumber] = EARLIER ( [@previousdaynumber] )
                    ),
                    [@WorkDay]
                )
                    = BLANK (),
                1,
                0
            )
    )
VAR _addgroupflag =
    ADDCOLUMNS (
        _addflag,
        "@group", SUMX ( FILTER ( _addflag, [@daynumber] <= EARLIER ( [@daynumber] ) ), [@flag] )
    )
VAR _addnumberflag =
    ADDCOLUMNS ( _addgroupflag, "@numberflag", IF ( [@WorkDay] <> BLANK (), 1, 0 ) )
VAR _addresult =
    ADDCOLUMNS (
        _addnumberflag,
        "@result",
            IF (
                [@WorkDay] = BLANK (),
                BLANK (),
                SUMX (
                    FILTER (
                        _addnumberflag,
                        [@group] = EARLIER ( [@group] )
                            && [@daynumber] <= EARLIER ( [@daynumber] )
                    ),
                    [@numberflag]
                )
            )
    )
RETURN
    MAXX (
        FILTER (
            SUMMARIZE ( _addresult, [@daynumber], [@WorkDay], [@result] ),
            [@WorkDay] = Data[Work Day]
        ),
        [@result]
    )

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Hello Kh.Jihwan_Kim,

 

I have some problem when datatable add some data and condition as table below :

Siriwat_Lukin_0-1658888530034.png

I try to modify from yours reply but can't solve it.

Could you please suggest for solve this problems ?

 

Thank in advance again.

Hi,

If you have a date column together, the solution might be simpler.

Please check the attached file and the below DAX formula.

It is for creating a new column.

 

Rank result CC = 
VAR _currentdate = Data[Date]
VAR _currentname = Data[Name]
VAR _flagtable =
    ADDCOLUMNS ( Data, "@flag", IF ( Data[Work/Off] <> BLANK (), 0, 1 ) )
VAR _groupingtable =
    ADDCOLUMNS (
        _flagtable,
        "@group",
            SUMX (
                FILTER (
                    _flagtable,
                    Data[Date] <= EARLIER ( Data[Date] )
                        && Data[Name] = EARLIER ( Data[Name] )
                ),
                [@flag]
            )
    )
VAR _ranking =
    ADDCOLUMNS (
        _groupingtable,
        "@rank",
            IF (
                Data[Work/Off] <> BLANK (),
                RANKX (
                    FILTER (
                        _groupingtable,
                        Data[Name] = EARLIER ( Data[Name] )
                            && [@group] = EARLIER ( [@group] )
                            && Data[Work/Off] <> BLANK ()
                    ),
                    Data[Date],
                    ,
                    ASC
                )
            )
    )
RETURN
    MAXX (
        FILTER ( _ranking, Data[Date] = _currentdate && Data[Name] = _currentname ),
        [@rank]
    )

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Dear

 

It's work, Thank you very much 😊

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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