I created a new Datetime table that runs over the past year called master. It has another column for ID1 which is a crossjoin of another table with just the ID1 values(just 5 diffferent IDs) this in effect duplicated the datetime 5 times, one for each ID1.
Date | ID1 | UID |
4/7/2022 13:17 | A | |
4/7/2022 13:18 | A | |
4/7/2022 13:19 | A | |
4/7/2022 13:20 | A | 123ABC |
4/7/2022 13:21 | A | |
4/7/2022 13:16 | A | |
4/7/2022 13:17 | B | |
4/7/2022 13:18 | B | |
4/7/2022 13:19 | B | 224DFH |
I have a second table called ID2 that has a Unique identifer in it that I want to bring over to the master table in a new column. the table looks like this:
UID | ID1 | Datetime | ID3 | ID4 |
123ABC | A | 4/7/2022 13:20 | L | M |
224DFH | B | 4/7/2022 13:19 | O | P |
468ADD | A | 4/7/2022 13:16 | F | D |
I have been adding a new column to the master table using lookupvalue and calculate(sum(... and neither formula seems to bring any values over. Ideally the red text should appear. If I manually filter the table all of the conditions exist in the master table where the unique Identifier should be brought over yet nothing appears and I see no errors. The tables can only be related together with a many to many relationship that I have set up to filter both ways.
Dax looks like this:
UID = LOOKUPVALUE(
ID2[UID],
ID2[Datetime], Master[DateTime],
ID2[ID1],Master[ID1])
)
And
UID = calculate(
sum(ID2[UID]),
Filter(Master[Datetime] = ID2[Datetime]),
Filter(Master[ID1] = ID2[ID1])
)
Can someone help point me In the right direction in order to get the Unique identifier into the master table?
