Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe 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.
Hello
I'm Siriwat, I try to create index column as example table below (from Excel)
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^^
Solved! Go to Solution.
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.
Dear Jihwn_Kim
The answer is correct.
Thank you very much. i will learn from you formula and try to understand.
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.
Hello Kh.Jihwan_Kim,
I have some problem when datatable add some data and condition as table below :
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.
Dear
It's work, Thank you very much 😊
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
82 | |
79 | |
52 | |
39 | |
35 |
User | Count |
---|---|
92 | |
79 | |
51 | |
48 | |
45 |