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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jsierkstra
Frequent Visitor

How to model date relationship on multiple date columns

Hi,

 

I have a problem which seems easy to solve but I am not able to. Let's explain. I will simplify this in order to make it more clear.

 

I have a table, let's callet it Pallets. This table has three columns. Whenever Date Out has been set to 01-01-1753, it means that the pallet is still in stock.

Pallet CodeDate InDate Out
12314-11-202220-11-2022
45614-11-202201-01-1753
78921-11-202221-11-2022

 

I have a date table with a fixed amount of dates.

Date
14-11-2022
...
21-11-2022

 

I want to present a slicer to the user in which they can select an individual date.

 

In this simplified example, when the user selects the date 14-11-2022, I want the following rows to be visible.

  • 123 -> Yes
    • It was in stock on 14-11-2022 and out of stock on 20-11-2022 so that falls in the range for 14-11-2022.
  • 456 -> Yes
    • It was in stock on 14-11-2022 and not yet out of stock. So that falls in the range.
  • 789 -> No
    • It was not in stock on 14-11-2022 so that does not fall in the range.

When they select 21-11-2022, I want the following rows to be visible.

  • 123 -> No
    • It was out of stock on 20-11-2022 which is before the selected date.
  • 456 -> Yes
    • It was in stock on 14-11-2022 and not yet out of stock on the selected date.
  • 789 -> Yes
    • It was in and out of stock on the same day, but it was the selected date.

 

-----------------------------------------------------------------------------

 

This can be easily solved in SQL with the following (psuedo-)query. 

SELECT *

FROM Pallets

WHERE [Date In] <= 'Selected date'

AND ([Date Out] => 'Selected date'  OR [Date Out] = '01-01-1753')

 

I tried the following things with mixed results:

  • Create a table with the Dax-function: CALCULATETABLE(). I pass a filter to this function, but whenever I update my selected date, the calculated table won't update. 
  • I created a directquery table and added parameters. But I was not succesfull in getting it to work whenever my date changes. 
  • Created two relations from my date table to my Pallets table, one from date table to [Date In] and one from date table to [Date Out]. But this does not work because I can't specify the filter behaviour of the relationship. Besides, this creates one active relationship and one inactive. 

Is there a solution to this problem? 

 

---------------------------------------------------------

 

I cannot upload .pbix files directly to this forum, so I added a power bi file to my OneDrive.

https://1drv.ms/u/s!AoS_6AkTEXJzgYAPiWoBhj42BOneLQ?e=j5RNhX

 

1 ACCEPTED SOLUTION
TonyZhou1980
Resolver I
Resolver I

I tried to create a measure as below, and in the visual, set the "Still in Stock" in slice pane to equal to "yes", add a data[data] slice,

Still in Stock =
var _datein = calculate(max('palletcode'[Date In]),allexcept(palletcode,palletcode[Date In]))
var _dateout = CALCULATE(max('palletcode'[Date Out]),allexcept(palletcode,'palletcode'[Date Out]))
Var result =
    if(
        selectedvalue('Date'[Date])>=_datein && or(selectedvalue('Date'[Date])<=_dateout,_dateout=date(1753,1,1)),
        "Yes",
        "No"
    )
return
        if(ISINSCOPE(palletcode[Date In]),result,BLANK())
 
If you know how to attach the sample file, please tell me then I can attach the file for your reference.
 

View solution in original post

9 REPLIES 9
TonyZhou1980
Resolver I
Resolver I

I tried to create a measure as below, and in the visual, set the "Still in Stock" in slice pane to equal to "yes", add a data[data] slice,

Still in Stock =
var _datein = calculate(max('palletcode'[Date In]),allexcept(palletcode,palletcode[Date In]))
var _dateout = CALCULATE(max('palletcode'[Date Out]),allexcept(palletcode,'palletcode'[Date Out]))
Var result =
    if(
        selectedvalue('Date'[Date])>=_datein && or(selectedvalue('Date'[Date])<=_dateout,_dateout=date(1753,1,1)),
        "Yes",
        "No"
    )
return
        if(ISINSCOPE(palletcode[Date In]),result,BLANK())
 
If you know how to attach the sample file, please tell me then I can attach the file for your reference.
 

Hey, thanks for the effort. Unfortunately your proposed solution does not work. Whenever I select the date 14-11-2022, all the correct records show up. However, when I select 15-11-2022, none show up. This is incorrect behaviour because everything that was in stock on the 14th should also be in stock on the 15th. Because the date out are past 15-11-2022

 

Here is the attached file:

https://1drv.ms/u/s!AoS_6AkTEXJzgYARGfj95uSsY0iuYQ?e=XpulQB

@TonyZhou1980 I misunderstood your solution. After thinkering with it, it finally clicked. It is working now! For completeness I will upload the working file. One of the problems that was interfering the correct working is that I still had relationships on the date table <-> pallets. This caused the returned records to be incorrect. Once I removed those, it works.

 

This is the working file.

https://1drv.ms/u/s!AoS_6AkTEXJzgYASSogDptsVzne2qw?e=DBValY

Hi @jsierkstra ,

 

Please remove the relationship between these two tables.

vcgaomsft_1-1669706637927.png

In the formula, they pass a value here for filtering. This is fine, no additional filters are needed.

vcgaomsft_2-1669706711602.png

 

vcgaomsft_0-1669706624705.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

lbendlin
Super User
Super User

Read about inactive relationships and how to activate them with USERELATIONSHIP().

Hi, thanks for your suggestion. Unfortunately I am not able to get it to work with USERELATIONSHIP()

 

I tried this

Pallets in stock = CALCULATETABLE('Pallets', FILTER('Pallets', [Date In] < Datetable[Date]), USERELATIONSHIP('Pallets'[Date In], Datetable[Date]))
 
But this give me the error that I need to use an aggregate function for the datetable. Whenever I use something like this:
Pallets in stock = CALCULATETABLE('Pallets', FILTER('Pallets', [Date In] < SELECTEDVALUE(Datetable[Date])), USERELATIONSHIP('Pallets'[Date In], Datetable[Date]))
 
It does not give me any result. If I use MAX(Datetable[Date]) or MIN(Datetable[Date])), it does not apply the correct selected date. It just picks the minimum/maximum value from the date table and applies that. 

Whenever Date Out has been set to 01-01-1753, it means that the pallet is still in stock.

Please reconsider this approach. It is much better to leave the value blank instead of specifying a very bad date.

I agree with you, but leaving the value blank does not change the logic. It just replaces the check from 1-1-1753 to blank.

It dramatically reduces the size of the auto date tables.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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