The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I am fairly new to PowerBI and ive been struggling to try and get the correct data, i feel like i am going in the right direction but i need some assistance.
I have a Data Query that pulls the following data from an SQL Server:
ITEM | DATEUSED | DATERETURNED | USEDBY |
ITEM 1 | 2024-01-01 | 2024-01-02 | PERSON1 |
ITEM 2 | 2024-01-03 | 2024-01-10 | PERSON2 |
ITEM 3 | 2022-10-01 | 2023-10-01 | PERSON3 |
I also have a custom CALENDARAUTO table that i use for date selecting via a slicer.
I would like to be able to select a day or range of days using the slicer and then show when an item has or hasnt been used over that period, and if a particular item has not been used then i would like it to be shown with no data.
(DATEUSED and DATERETURNED are inclusive).
e.g. Output (if i select the date range 2024-01-01 - 2024-01-03)
DATE | ITEM1 | ITEM2 | ITEM3 |
2024-01-01 | PERSON 1 | ||
2024-01-02 | PERSON 1 | ||
2024-01-03 (and so on) | PERSON 2 |
I have tried going down the path of a merge query to merge the calendar table with the data table to get a combined table but it is only showing rows where data exists.
I dont know if what i wasnt is even possible, please can someone assist?
Thanks
Solved! Go to Solution.
to know how to do this watch my video and pbix File
List.Generate(()=>[DATEUSED],
(x)=> x<=[DATERETURNED],
(x)=> Date.AddDays(x,1))
This is amazing, thank you.
I've come across a few potential issues with this method though.
There are a total of around 600+ items, meaning that each item needs to be manually added to the table as a column, and new items will also have to be manually added.
- Ive tried merging these into their own table so it can be used for filtering etc, however again each column needs to be typed manually to merge?
show me what your table looks like so I can understand what you need
Thank You.
This is essentially the output table i am trying to get:
With the ability to filter on the item.
But if each ITEM is added as its own column, would i need to aggregate all of these columns into a single column so that i can filter them?
But if each ITEM is added as its own column
why do you have them separately and how is it I don’t understand
Sorry.
Following your video.
Pivoting on the ITEM column adds each ITEM as its own column into the table.
this is so you can filter by date
Thank You.
Using both your solutions i've managed to get the data to display how i require.
I appreciate your help.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
to know how to do this watch my video and pbix File
List.Generate(()=>[DATEUSED],
(x)=> x<=[DATERETURNED],
(x)=> Date.AddDays(x,1))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |