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
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 Code | Date In | Date Out |
123 | 14-11-2022 | 20-11-2022 |
456 | 14-11-2022 | 01-01-1753 |
789 | 21-11-2022 | 21-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.
When they select 21-11-2022, I want the following rows to be visible.
-----------------------------------------------------------------------------
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:
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
Solved! Go to Solution.
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,
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,
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:
@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.
Hi @jsierkstra ,
Please remove the relationship between these two tables.
In the formula, they pass a value here for filtering. This is fine, no additional filters are needed.
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
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |