Treatas with Table Variable

DAX DAX Calculations
1

I am creating a measure that contains a TableVariable that includes a date column. My Result is a SUMX over this Table variable and then I am surrounding the sumx with Calculate so I can use Treatas to relate the date column in my TableVariable to my Date table. However I can’t figure out how to reference the Date column in my table variable within the Treatas function. Any suggestions?

Here is Mesaure. Thanks for your suggestions.

Capacity Hours =
VAR _MinDate = MIN(ProductionOrderRouting[StartingDate])
VAR _MaxDate = MAX(ProductionOrderRouting[EndingDate])
VAR _WOCalendar = CALENDAR(_MinDate,_MaxDate)

VAR _BaseTable =
CROSSJOIN(
SUMMARIZE(
ProductionOrderRouting,
ProductionOrderRouting[Type],
ProductionOrderRouting[WorkCenterNo]
),
_WOCalendar
)

VAR _BaseTable2 = \I added this to change the name of the date column for testing
SELECTCOLUMNS(
_BaseTable,
“@type”, ProductionOrderRouting[Type],
“@WorkCenter”,ProductionOrderRouting[WorkCenterNo],
“@Date”,[Date]
)

VAR _BaseTableWithHours =
ADDCOLUMNS(
_BaseTable2,
“Hours”,
CALCULATE(
COUNTROWS( ProductionOrderRouting ),
FILTER( ProductionOrderRouting, ProductionOrderRouting[EndingDate] >= [@Date] ),
FILTER( ProductionOrderRouting, ProductionOrderRouting[StartingDate] <= [@Date] ),
FILTER( ProductionOrderRouting,ProductionOrderRouting[WorkCenterNo] = [@WorkCenter])
)
)

Var _Result =
calculate(
SUMX(_BaseTableWithHours,
[Hours]),
TREATAS(_BaseTableWithHours[@Date],DateDimension[Date]) //This is not working.
)

RETURN
_Result

2

Hi.
If you can provide a sample of the data you are working with and an explanation of what you want to achieve with the calculation, then people are likely to help get your query resolved on time and maybe with an efficient method.

However, by just looking at the code, the reason why TREATAS won’t work in this case is because the variable, _BaseTableWithHours, has four columns and so for TREATAS to work, you need to express what each of those columns should be “treated as”.

1 Like
3

Hi,
Thanks for your reply. OK, I have mocked up a very simplified example of what I am trying to accomplish. To explain in words…

  1. I have a table of work orders with a StartingDate and EndingDate.
  2. I am trying to create a visual that lists Calendar dates as rows and then a measure that will count up how many orders are active on each date.
  3. I am able to create a table that summarizes this data and then I am able to connect that table to the Dates table and sum the days column. (‘WO Schedule Summary Table’)
  4. However I was trying to figure out if I could make this same calculation in a measure without having to create the extra table.
  5. You will see in the visual that the first measure is against the Summary table and it is working. However the next two measures are attempts at bypassing the table and neither will use the Treatas relationship to the date table.
    Mfg Template.pbix (64.9 KB)

Example file is attached and thanks for your help. Please let me know if you have further questions.

1 Like
4

Hi.
I have come up with a solution that gets the results as you described without using the WO Schedule Summary Table but by creating a relationship between the WOList table and the Dates table. The measure is called “Active Work Orders” and you can find the .pbix file containing the solution.

Mfg Template - eDNA forum - patrickcox.pbix (66.9 KB)

Active Work Orders =
VAR CurrentDates =
    VALUES( Dates[Date] )
VAR WorkOrderswithDates =
    GENERATE(
        ALLSELECTED( WOList ),
        DATESBETWEEN( Dates[Date], WOList[StartDate], WOList[EndDate] )
    )
VAR ActiveOrdersWithDates =
    FILTER( WorkOrderswithDates, [Date] IN CurrentDates )
VAR ActiveOrders =
    DISTINCT(
        SELECTCOLUMNS( ActiveOrdersWithDates, "OrderNum", WOList[OrderNum] )
    )
RETURN
    COUNTROWS( ActiveOrders )

May not be the most efficient but it works.

Result Table
Model Relationship

1 Like
5

Thanks for spending time on this. I have looked this over and I don’t really follow what the measure is actually doing. LOL. It looks like I don’t even need the join to the WOList for it to work. Ultimately I am trying to get to a sum of hours and I plan to include start time and end time so I don’t know that the Distinct count will work for that.

I will think about it some more but one follow up question, since you didn’t pursue the Treatas solution, does that mean it was not possible to create a Treatas relationship between the Dates table and the Table Variable I created with the list of dates using GENERATE? That is what I was really trying to figure out. I am pretty sure there is no data lineage to the Dates column I created with Generate and I was trying to use Treatas to create that lineage. But maybe that is not possible with a table variable.

Thanks again for your help and no worries if you are not sure on my last question.

Thanks!

1 Like
6

My bad. I should have provide an explanation on the logic behind the code.

My thought process is this. First store the date(s) in the current filter context in a variable, CurrentDates. Then, WorkOrdersWithDates generates a new column of dates for each WorkOrder from the StartDate till the EndDate of each WorkOrder. The FILTER in the ActiveOrdersWithDates then checks if each of the dates from WorkOrderswithDates is also present in the currently filtered dates. Lastly, I used the DISTINCT because of the totals and grand totals. At the grand total level, WorkOrdersWithDates would contain more rows than the WOList table so the result of the measure would make no sense.

As for the TREATAS question, You can use a table variable with the TREATAS. But I haven’t figured out how to come up with a solution to your query using TREATAS.

Currently, what I came up with may not work with SUM. I’ll test it out and provide something better if I can come up with one.

Regards.

1 Like
7

Hi,
No worries. You don’t have to spend anymore time on this. I know you are busy. This has been helpful and thanks again for your time.

Regards,
Patrick

2 Likes