Been scrolling through the forums but haven't found an answer to this yet (likely searching the wrong terms), hoping someone here can point me in the right direction. I'm just coming back to the Data Analytics field after an extended period away so trying to shake off that rust. I hope the format I put it in below is easy to follow and that I haven't doubled up on this question. The OverviewI have two tables dimDates and Transactions (in fact there are two Transaction tables but will keep this example with one as they are independent) with a relationship based on a dateKey field that is "DimDates 1>* Transactions". Tables look something like this (created with random dates just to demonstrate example date table is a full featured table of dates):DateDateKey2019-05-2312020-07-3022020-01-0832022-06-254 DateKeyAccountAmount3A202A302B41C5 The IssueI'm looking to create a card that displays the latest transaction date and total transactions with a visual filter based on Account. So a card filtered for Account A would display "2020-07-30" and "50". Unfortunately when I do this currently due to the direction of the relationship I only get the last date of the DimDates table "2022-06-25". Attempts to resolveI attempted to try resolve this with changing both direction and it solves the problem for the card but creates no end of issues for other visuals such as rolling totals, so that wasn't viable. I attempted to solve it with a measure using the DAX Related but it can't seem to interact with the DimDates table for some reason.

Here is the solution: Model view: I usually don't ask, but this time I'd appreciate an Accepted solution with a thumbs up. 😁 Cheers!

Aklys

Helper I

2 years ago

Solved

Max Date from Dim table using Fact table filter applied

Been scrolling through the forums but haven't found an answer to this yet (likely searching the wrong terms), hoping someone here can point me in the right direction. I'm just coming back to the Data Analytics field after an extended period away so trying to shake off that rust. I hope the format I put it in below is easy to follow and that I haven't doubled up on this question.

The Overview
I have two tables dimDates and Transactions (in fact there are two Transaction tables but will keep this example with one as they are independent) with a relationship based on a dateKey field that is "DimDates 1>* Transactions". Tables look something like this (created with random dates just to demonstrate example date table is a full featured table of dates):

Date	DateKey
2019-05-23	1
2020-07-30	2
2020-01-08	3
2022-06-25	4

DateKey	Account	Amount
3	A	20
2	A	30
2	B	4
1	C	5

The Issue

I'm looking to create a card that displays the latest transaction date and total transactions with a visual filter based on Account. So a card filtered for Account A would display "2020-07-30" and "50". Unfortunately when I do this currently due to the direction of the relationship I only get the last date of the DimDates table "2022-06-25".

Attempts to resolve
I attempted to try resolve this with changing both direction and it solves the problem for the card but creates no end of issues for other visuals such as rolling totals, so that wasn't viable. I attempted to solve it with a measure using the DAX Related but it can't seem to interact with the DimDates table for some reason.

Need Help

Element115
2 years ago
Here is the solution:

Model view:

I usually don't ask, but this time I'd appreciate an Accepted solution with a thumbs up. 😁 Cheers!