Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Good afternoon,
Please could someone help me with an issue I am struggling with. I have a Calendar dimension table that contains flags ("Y") for all UK Bank Holidays.
I am trying to create an expression where:
The formula (which is not working) is:
NetWorkDays Summarize variable=
VAR colHolidays =
SUMMARIZE(FILTER(dimCalendar,dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
RETURN
NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays)
The reason I thought this would work is I was able get the correct results by doing this in two stages:
First by creating a table using that SUMMARIZE formula
Table =
SUMMARIZE(FILTER(dimCalendar,dimCalendar[flgHoliday]="Y"),
dimCalendar[Date])
And then I referenced that table in a NETWORKDAYS expression and it worked:
NetworkDays SUMAMRIZE Table = NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,'Table')
Here is a screenshot of the table of results:
Please can you help me fix my variable definition?
Kind regards,
Paul
Solved! Go to Solution.
@PaulVanS267 Yep, that did it, here is the fix:
NetWorkDays Summarize Variable = //Not returning the correct result
VAR colHolidays =
SUMMARIZE(FILTER(ALL(dimCalendar),dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
RETURN
NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays)
@PaulVanS267 Very odd. I can't replicate that. NETWORKDAYS accepts a virtual table as the 4th argument. PBIX is attached below signature.
Thank you for reaching out.
I have attached the pbix for you to look at. Perhaps it will be easier to identify the issue?
Kind regards,
Paul
@PaulVanS267 Yep, that did it, here is the fix:
NetWorkDays Summarize Variable = //Not returning the correct result
VAR colHolidays =
SUMMARIZE(FILTER(ALL(dimCalendar),dimCalendar[flgHoliday]="Y"),dimCalendar[Date])
RETURN
NETWORKDAYS(MAX(Projects[start_date]),MAX(Projects[end_date]),1,colHolidays)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
76 | |
55 | |
37 | |
34 |
User | Count |
---|---|
99 | |
56 | |
53 | |
44 | |
40 |