Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Aklys
New Member

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):

DateDateKey
2019-05-231
2020-07-302
2020-01-083
2022-06-254

 

DateKeyAccountAmount
3A20
2A30
2B4
1C5

 

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.

1 ACCEPTED SOLUTION
Element115
Impactful Individual
Impactful Individual

Here is the solution:

 

Screenshot 2024-02-21 185641.jpg

 

Screenshot 2024-02-21 185734.jpg

Screenshot 2024-02-21 185814.jpg

Screenshot 2024-02-21 185853.jpg

 

Model view:

Screenshot 2024-02-21 185949.jpg

 

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

View solution in original post

7 REPLIES 7
Element115
Impactful Individual
Impactful Individual

Here is the solution:

 

Screenshot 2024-02-21 185641.jpg

 

Screenshot 2024-02-21 185734.jpg

Screenshot 2024-02-21 185814.jpg

Screenshot 2024-02-21 185853.jpg

 

Model view:

Screenshot 2024-02-21 185949.jpg

 

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

That worked a charm. I thought for a second it wasn't working as  the cards were showing up blank. But I think it was because it tooks a while for the measures to populate.

Element115
Impactful Individual
Impactful Individual

Also, try showing ChatGPT 4 (use the Data Analyst GPT) the model with the relation and ask it to come up with the DAX code.  Should be interesting.

Unfortunately I won't use ChatGPT since it requires PII to utilise it. I had tried some local LLMs but they didn't quite cut it with their limited parameter size.

Element115
Impactful Individual
Impactful Individual

Isn't there a trial version...? https://openai.com/chatgpt

 

Or it might be $20/mo.

Wasn't the cost issue. It was the need to give your mobile phone to them to use it.

Element115
Impactful Individual
Impactful Individual

Weird, should be instant.  At least, it is on my PC. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.