Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Here's the scenario: Given a row in a table ("Intervention) with a set of two datetime columns ("Start" and "End") I want to compute the difference between the two timestamps excluding certain days in between (let's say saturday and sunday).
First approach would be to create a table variable with a calendar of these two dates
Solved! Go to Solution.
@lbendlin You can't use CALCULATE with virtual tables. It is the main reason I do not use CALCULATE very often:
You will want to use SUMX, AVERAGEX, MAXX, etc.
And, for what you are originally doing, you could just use ([End] - [Start]) * 1. or DATEDIFF
@lbendlin
Agree with @Greg_Deckler's points and the explanations.
@lbendlin
So in your case below approach should working, interested to know how you approached it.
Difference =
var db = CALENDAR(Intervention[Start],Intervention[End])
return
SUMX(db,IF( WEEKDAY([Date],2) > 5, 1,0))
Notice the RED highlighting, it's still valid though.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Yes, that was one of the options I eventually presented to the OP
Difference = DATEDIFF(Intervention[Start],Intervention[End],MINUTE)-1440*sumx(CALENDAR(Intervention[Start],Intervention[End]),if(WEEKDAY([Date],2)>5,1,0))
I first tried to use COUNTAX but that doesn't seem to work well with binary values
Difference = DATEDIFF(Intervention[Start],Intervention[End],MINUTE)-1440*COUNTAX(CALENDAR(Intervention[Start],Intervention[End]),WEEKDAY([Date],2)>5)
won't actually give the expected result.
@lbendlin You can't use CALCULATE with virtual tables. It is the main reason I do not use CALCULATE very often:
You will want to use SUMX, AVERAGEX, MAXX, etc.
And, for what you are originally doing, you could just use ([End] - [Start]) * 1. or DATEDIFF
Thank you. As i mentioned in my update ADDCOLUMNS works too, even though the DAX intellisense complains passive-aggressively.
@lbendlin Yeah, if you look at most of my DAX in things like the Quick Measures Gallery, I use tons of virtual tables. Everything in DAX pretty much works with virtual tables except CALCULATE and a few other functions that require physical tables like MAX, MIN, etc. so you have to use the equivalents MAXX, MINX, etc. But yeah, ADDCOLUMNS works great, I use ADDCOLUMNS with virtual tables a ton!
I think the iffy part is that you need to know the column names that some of these virtual tables generate. Sometimes it seems to be [Value] but in the CALENDAR() example it is [Date] etc. Would be nice if DAX could address table columns by number.
@lbendlin Yeah, it's always Value except for the Calendar. Intellisense/type ahead does work for these virtual tables. If I am not mistaken I believe: { (4, 5, 6), (7, 8, 9) } creaes Value1, Value2, Value3 columns but if just a single column gets created it is always Value unless it is from a Calendar function. If you create a table from a physical table,
VAR __Table = 'Table'
The names of the columns are the same as in the physical table. For most other ways of creating virtual tables or adding columns you explicitly name them.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |