Forum Discussion
DAX - Column content based on last Date slicer selection
Hello guys, I'm trying to create a column in the fSales with the last status of the order according to the last selected date. I have a fact Sales, Calendar and a fact Historical Sales fSales ...
Hi Anonymous ,
NewStatusOrder = VAR LatestDT_Status = CALCULATE ( MAX ( fHistoricalSales[DT_Status] ), FILTER ( ALLSELECTED ( fHistoricalSales ), fHistoricalSales[id_Order] = SELECTEDVALUE ( fHistoricalSales[id_Order] ) ) ) RETURN CALCULATE ( SELECTEDVALUE ( fHistoricalSales[Status] ), FILTER ( fHistoricalSales, fHistoricalSales[DT_Status] = LatestDT_Status ) )Best regards,
Yuliana Gu
v-yulgu-msft
Microsoft Employee
Microsoft EmployeeHi Anonymous ,
NewStatusOrder =
VAR LatestDT_Status =
CALCULATE (
MAX ( fHistoricalSales[DT_Status] ),
FILTER (
ALLSELECTED ( fHistoricalSales ),
fHistoricalSales[id_Order] = SELECTEDVALUE ( fHistoricalSales[id_Order] )
)
)
RETURN
CALCULATE (
SELECTEDVALUE ( fHistoricalSales[Status] ),
FILTER ( fHistoricalSales, fHistoricalSales[DT_Status] = LatestDT_Status )
)
Best regards,
Yuliana Gu
Hello v-yulgu-msft , thanks for helping me.
The problem is that I have a 1 conection with Calendar[Date] and fSales[SalesDate] and a secondary connection with Calendar[Date] and fHistoricalSales[DT_Status]. Do you think using USERELATIONSHIP could help?
- v-yulgu-msft
Hi Anonymous ,
Make sure there existing an active relationship between fSales[id_order] and fHistoricalSales[id_Order], so, the relationships should be like below, above measure still works.
Best regards,
Yuliana Gu