Dimension based calculation options

[Anonymous]

We have dates at the dimension level as well as the fact.  Above the Dim dates should only calculate the unique start/end.  Is there a way to do this with calculate or other function ?  I thought about storing the dates in both dimension and fact then calculating DimDaysBetween then hding the dates in the dimension.  It would be ideal to have all the dates in the fact table to avoid multiple calendars.

[Anonymous · Accepted solution]

Read up on related vs relatedtable.

https://business-insights.net/2017/09/02/the-magic-of-related-relatedtable-functions-in-dax/

Since I want to go from the fact to the dimension, I need to use RELATEDTABLE

SLA = MINX(RELATEDTABLE(appt),appt[completesla])

I hid appt[completesla]

Next step is to aggregage SLA over the context.

[Anonymous]

Hi

I have done something similar in another context, but maybe you can use it here.

avg = AVERAGEX( SUMMARIZE(FactTable, FactTable[DimStartDate], FactTable[DimEndDate], FactTable[DimDaysBetween]), FactTable[DimDaysBetween])
sum= SUMX( SUMMARIZE(FactTable, FactTable[DimStartDate], FactTable[DimEndDate], FactTable[DimDaysBetween]), FactTable[DimDaysBetween])

If it works then please mark it as the solution.