Use/Reference a DAX measure that returns a table in multiple other DAX measures

[Deez]

Hi All,

Is it possible?
* Has to be done in DAX measure since this is a custom requirement and not reusable across clients *

Example:

-- Measure#1 Returns a single column table of dates (Sample Non working dates)

List Of Dates =
Var nonworkingdates =
Union(
ROW("PHDates", dt"2022-01-01"),
ROW("PHDates", dt"2022-01-03"),
ROW("PHDates", dt"2022-01-09")
)
Return
nonworkingdates

-- Measure#2 calculating number working dates (Excludes non working days from Measure#1 above and excludes Saturday and Sundays)

Non Working Days =
var mindate = MIN('Calendar'[Date])
var maxdate = MAX('Calendar'[Date])
Return
NETWORKDAYS (
mindate,
maxdate,
1, -- Ignore Saturday and Sunday
[List Of Dates] -- **** Accepts a table as input but does not recognise Measure#1 returns a table
)

I can do it all in Measure#2 i.e Measure#1 becomes a variable which I then reference and it works.
I want to do it in separate measures since the hardcoded list in Measure#1 will be reused across many other measures.
That list will be updated occasionally and best for the update to be in one place.

Any idea?

@v-yangliu-msft 
@marekr 

[v-yalanwu-msft · Accepted solution]

Hi, @Deez ;

You could create a measure :

Non Working Days = 

var mindate = MIN('Calendar'[Date])
var maxdate = MAX('Calendar'[Date])
return 
COUNTROWS (
FILTER (
ADDCOLUMNS (
CALENDAR ( mindate, maxdate ),
"Day of Week", WEEKDAY ( [Date], 1 )),
[Day of Week] <> 1&& [Day of Week] <> 7&&NOT( [Date] ) in VALUES('List Of Dates'[PHDates])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.