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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fetch dimension value dynamically based on Filter Context

Hi,


I am trying to fetch the corresponding title from last year onto a row with a new title this year.

 

Example is we have rows with Sales and Sales LY and then we want the Title and Title LY for that. How can I fetch the Title LY?

 

I added a picture as an example. The top is how it might look like in a table and the bottom is how I want the result to be.

 

Screnshot.PNG

I thought maybe I could do something like:


Title (LY) =

VAR vMaxDateLastYear = YEAR ( [MaxDate] ) - 1

VAR TitleID= MIN ( dim_issue[TitleID] )


RETURN


CALCULATE (
MIN ( dim_issue[Title] );
FILTER ( dim_issue; dim_issue[TitleID] = Utgavenummer );
dim_time[Year] = vMaxDateLastYear
)


Thanks for any replies.

Best,

Ali A

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

Hi @Anonymous ,

I created a calculated column with the function of EARLIER that you have a try.

Last Title = CALCULATE(MAX('Table'[Title]),FILTER('Table','Table'[Title ID] = EARLIER('Table'[Title ID]) && 'Table'[Year] +1 = EARLIER('Table'[Year])))

1.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-xuding-msft 

 

This seems like it is correct, but I am having some issues using it in my code. I suspect that it is because it is not in the same model.

 

Yessir.PNG

Anonymous
Not applicable

So after trying some back and forth what did it was including a CrossFilter!

Title (Current Year) =
VAR vCurrentYearBasedOnMaxDate =
YEAR ( [Last Chosen Date] )
VAR Utgavenummer =
MIN ( dim_issueTitleID)
RETURN
CALCULATE (
MIN ( dim_issueTitle);
dim_time[Year] = vCurrentYearBasedOnMaxDate;
CROSSFILTER ( dim_issue[pk_dim_issue]; fact_issue[fk_dim_issue]; BOTH )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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