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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EnderWiggin
Helper I
Helper I

Filter Calendar lookup table based on fact table value

Hi All,

I would like to ask your help to find a solution of the following case.

I would like to get the minimum Calandar[Date] value which is equal or greater than TransctionPast[FirstValid] date.

Here are the relationships:

EnderWiggin_1-1635936691913.png

I created the following measure but it just uses a lot of RAM without any result, when I tried to put it in it in a power pivot's value section

EnderWiggin_2-1635936929233.png

I think it is caused by the relationship and the filter direction but I can not figure out how to get the required date value. 

I would use it for further calcualtion which will be in the value section of a power pivot.

Could you help me to define the required measure? Please see the sample file on the following link. Thank you very much in advance!

Power Pivot Sample 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@EnderWiggin 

You would not return anything if you compare Date with MIN('TransactionPast'),[FirstValid]). If you would to compare Calendar date with minimum date in [FirstValid] column.

 

Try:

Measure = CALCULATE(MIN('Calendar'[Date]),FILTER('Calendar',[Date]>=MINX(ALL('TransactionPast'),[FirstValid])))
 
 
Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
EnderWiggin
Helper I
Helper I

@V-pazhen-msft, you are right in some cases the 'TransactionPast'),[FirstValid]) value was not available becasue of the filter context, so this was the root cause of the high memory usage. Your measure works, so thank you very much  for the solution and your response!

V-pazhen-msft
Community Support
Community Support

@EnderWiggin 

You would not return anything if you compare Date with MIN('TransactionPast'),[FirstValid]). If you would to compare Calendar date with minimum date in [FirstValid] column.

 

Try:

Measure = CALCULATE(MIN('Calendar'[Date]),FILTER('Calendar',[Date]>=MINX(ALL('TransactionPast'),[FirstValid])))
 
 
Best regards
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors