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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Convert DAX formula to Power Query formula

Hi,

 

I have created the following DAX formula:

_____________________________________________________________________________________________________________________________________

Workday Seconds =
VAR vWorkdayStartHour = 8
VAR vWorkdayEndHour = 17
VAR vColumn1 = bw_Cofiwerkorders[Start_werk]
VAR vColumn2 = bw_Cofiwerkorders[Datum_afgehandeld]
VAR vWorkdayHours = vWorkdayEndHour - vWorkdayStartHour
// the earlier of the two columns
VAR vStartDatetime =
      MIN ( vColumn1, vColumn2 )
// the later of the two columns
VAR vEndDatetime =
      MAX ( vColumn1, vColumn2 )
VAR vStartDate =
      INT ( vStartDatetime )
VAR vEndDate =
      INT ( vEndDatetime )
// count the workdays between StartDate and EndDate (non-inclusive)
VAR vWorkdaysBetween =
      CALCULATE (
             COUNTROWS ( Dim_Date ),
             Dim_Date[Date] > vStartDate,
             Dim_Date[Date] < vEndDate,
             Dim_Date[WorkingDays] = 1
)
// total workday seconds between StartDate and EndDate (non-inclusive)
VAR vWorkdaySecondsBetween = vWorkdaysBetween * vWorkdayHours * 60 * 60
// Start Date beginning of day
VAR vStartDateBOD = vStartDate + TIME ( vWorkdayStartHour, 0, 0 )
// Start Date end of day
VAR vStartDateEOD = vStartDate + TIME ( vWorkdayEndHour, 0, 0 )
// End Date beginning of day
VAR vEndDateBOD = vEndDate + TIME ( vWorkdayStartHour, 0, 0 )
// End Date end of day
VAR vEndDateEOD = vEndDate + TIME ( vWorkdayEndHour, 0, 0 )
VAR vStartDatetimeAdj =
         SWITCH ( TRUE (),
         vStartDatetime < vStartDateBOD, vStartDateBOD,
         vStartDatetime > vStartDateEOD, vStartDateEOD,
         vStartDatetime
)
VAR vEndDatetimeAdj =
         SWITCH ( TRUE (),
         vEndDatetime < vEndDateBOD, vEndDateBOD,
         vEndDatetime > vEndDateEOD, vEndDateEOD,
         vEndDatetime
)
VAR vStartDateSeconds = DATEDIFF ( vStartDatetimeAdj, vStartDateEOD, SECOND )
VAR vEndDateSeconds = DATEDIFF ( vEndDateBOD, vEndDatetimeAdj, SECOND )
VAR vTotalWorkdaySeconds =
         SWITCH ( TRUE (),
         vStartDate = vEndDate, DATEDIFF ( vStartDatetimeAdj, vEndDatetimeAdj, SECOND ),
         vStartDateSeconds + vWorkdaySecondsBetween + vEndDateSeconds
)
VAR vResult = IF ( vColumn1 < vColumn2, vTotalWorkdaySeconds, vTotalWorkdaySeconds * -1 )
RETURN
          vResult
___________________________________________________________________________________________________________________________________
When I want to convert this formula to Power Query, it gives me errors on CALCULATE and COUNTROWS.
Are there any alternatives for this part of the formula, so it works in Power Query?
3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

Change the CALCULATE VAR to:

VAR vWorkdaysBetween =
      CALCULATE (
             COUNTROWS (
FILTER(Dim_Date,  Dim_Date[Date] > vStartDate &&
             Dim_Date[Date] < vEndDate &&
             Dim_Date[WorkingDays] = 1
))




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






lkalawski
Super User
Super User

Hi @Anonymous ,

Please send in the Power Query code you wrote.

There is no Calculate or Countrows in Power Query.

Anonymous
Not applicable

Hi @lkalawski 

The outcome of this DAX formula, I would like to get in Power Query . I would like to see some alternatives for the CALCULATE and COUNTROWS, so I can get a working Power Query formula.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.