Reputation: 1735
I have three table namely Allitems, InItems and OutItems. The main table is Allitems which is related to the other two table with IN_OUT_ID. I have been able to calculate the min and max date based on the IDs for each of the
Dataset
table using the below DAX Formula
For InItems tables
Min_Start_Date = CALCULATE( MIN(InItems[InDate]), ALLEXCEPT( InItems, InItems[IN_ID] ))
Max_end_Date = CALCULATE( MAX(InItems[InDate]), ALLEXCEPT( InItems, InItems[IN_ID] ))
For OutItems table
Min_Start_Date = CALCULATE( MIN(OutItems[OutDate]), ALLEXCEPT( OutItems, OutItems[OUT_ID]))
Max_end_Date = CALCULATE( MAX(OutItems[OutDate]), ALLEXCEPT( OutItems, OutItems[OUT_ID] ))
the relationship i have change the direction to flow both ways
I am trying to bringback min and max date by comparing the the min and max date from OutItems and Allitems and use it as a column in Allitems table. For example taking IN_ID 1, the Min date is 08/01/2019. I will also take the min date of OUT_ID which is 03/02/2019. Then I want the min date between these two date which is 08/01/2019. Can anybody help on how I can achieve this
Expected outcome
I am open to any question thanks
Upvotes: 1
Views: 4846
Reputation: 40244
To do this, you don't need relationships to filter both ways and you don't need those four calculated columns. Just take the min/max of the min/max:
Mindate =
MIN (
CALCULATE ( MIN ( InItems[InDate] ) ),
CALCULATE ( MIN ( OutItems[OutDate] ) )
)
and
Maxdate =
MAX (
CALCULATE ( MAX ( InItems[InDate] ) ),
CALCULATE ( MAX ( OutItems[OutDate] ) )
)
Note that the CALCULATE
here performs a context transition that applies the row context (the IN_OUT_ID
and Item
values in the current row) as a filter context when taking the min/max over the other tables. If you remove it, you'd get the min/max across all ID values.
Upvotes: 2