Measure calculates correctly until related table field is added then duplicates

kamburn — Thu, 09 Nov 2023 03:13:10 GMT

Hi All-

I have a measure that is used to calulcate the last date record before a specific date. This measure works correctly as long as all of my fields are from the original table. However, if I add a field from a dimension table (relationship 1-many) the two tables duplicate all the values. The relationship also works fine when I do not have the measure added.

My Measure is:

Is Last Before Selection = var tbl1 = CALCULATETABLE(Filter(FactForecasts2, FactForecasts2[Date] <= [Selected Allocation Date]), ALLEXCEPT(FactForecasts2, FactForecasts2[Item], FactForecasts2[Group])) var maxDate = MAXX(tbl1, [Date]) return calculate(maxDate = Max([Date]))

Selected Allocation Date is also a measure getting the date from a slicer:

Selected Allocation Date = VALUES(Dates[Date])

On the report in Power BI I have 3 visuals.

All fields in the same FactForecasts table. The values of true in green boxes are correct.
No measure, but the first Group is from DimProductGroups and the second is from FactForecasts and they are Related on that field with 1 to Many. This also looks correct
This is the same as #2, but the Measue "Is Last Before Selection" is added. The rows are duplicated with each of the groups being aligned as if not related. The red box shows values where it has Group1 from one table and Group2 from the other table. Excluding these red boxes, the actual measue still works correctly where the two groups are actually correctly aligned.

The data that I have created is pretty simple.

DimProducts

Group	Description
Group1	Description 1
Group2	Description 2

FactForecasts

Group	Item	Date	Qty	Other
Group1	Item1	44927	100	X
Group1	Item1	45078	110	Y
Group1	Item1	45200	90	Z
Group1	Item2	45078	80	A
Group1	Item2	45170	70	B
Group2	Item1	44928	101	XX
Group2	Item1	45079	102	YY
Group2	Item1	45201	103	ZZ

Dates (For the slicer)

Date

1/15/2023

7/15/2023

10/15/2023

Thanks in advance for any insight.

topic Measure calculates correctly until related table field is added then duplicates in DAX Commands and Tips

Measure calculates correctly until related table field is added then duplicates