Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have spent DAYS, I'm serious... on trying to figure this out. I'm new to PBI and what I need is the following thing: it sounds easy, but I guess it is one of the hardest things to do in Power BI?
I have quite some queries, but the two most important queries are let's say: QueryA (contains the info) and QueryB (calendar)
Name of the table: PriceInfo --> contains 4 columns and 8000 rows
Key (link to my calendar) | A | B | C |
1 | € 1.50 | € 1.65 | 1.70 |
2 | € 1.70 | € 1.50 | € 1.15 |
... | ... | ... | ... |
8000 | € 1.10 | € 1.70 | € 1.65 |
Name of the table: Calendar --> contains all the date info
Key (link to my calendar) | Date | Weeknumber | Weekday | MonthName | Year | DayDate |
1 | 01/01/2009 | 1 | 4 | January | 2009 | 1 |
2 | 02/01/2009 | 1 | 5 | January | 2009 | 2 |
... | ||||||
8000 | 28/02/2020 | 9 | 5 | February | 2020 | 28 |
Since my data is connected to an API which refreshes every week, I need a measure or something that makes sure that my numbers are up-to-date every week. I have tried everything on this forum, but nothing works.
I would like to visualise in a Card the following:
And then some other cards with:
And then do exactly the same, but for months.
Could somebody help me out please? I feel like I would also need this type of thing for future projects, so all help is welcome!
Solved! Go to Solution.
@Gjakova I selected Feb 28th as a date. Just 3 measures and easy to maintain, solution attached. Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Gjakova oh ok, not sure why but comma was always there. Anyhow glad it is working. We did unpivot so that with few measures we can work on all the fuel categories, otherwise, we need to write measures for each column (each category) and that would have been 3 categories x 3 columns = 9 measures and also it is not scalable, and best practice. You cannot even filter if you had 3 columns and now can easily filter. Not sure if this answered your question but if you have any further questions, I can gladly explain.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k here are some extra screenshots for a clarification of what I adjusted to make it work:
Kudos are welcome if it gave you some clarification 😛
Hi there, it is already late on the other side of the world for screen sharing, but I appreciate the help!
Eventually your measure was correct, the only thing that was missing was a , before MONTH and before DAY.
Solution: ,DAY instead of DAY and ,MONTH instead of MONTH.
Maybe the Power BI settings are different in other regions of the world? Anyhow, thanks a lot for the measurement. Just one more question: why did I had to unipivot all the info except for ID, would it otherwise not have worked? (The thing you mentioned in your 2nd or 3rd post here).
Thanks a lot!
@Gjakova oh ok, not sure why but comma was always there. Anyhow glad it is working. We did unpivot so that with few measures we can work on all the fuel categories, otherwise, we need to write measures for each column (each category) and that would have been 3 categories x 3 columns = 9 measures and also it is not scalable, and best practice. You cannot even filter if you had 3 columns and now can easily filter. Not sure if this answered your question but if you have any further questions, I can gladly explain.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Ah I did not know that! Thanks a lot. So it is better to have more rows than columns? Or is that just the case when you want to create measures?
@Gjakova basic rule:
facts on which you do the calculations, usually are long and thin
dimension by which you measure facts, can be wide
if it makes sense. Anyhow, that comma thing is bizarre for me but I'm going to sleep over it for now. We already spent a lot of time on it and I'm glad it is working for you. Cheers!!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k hi there! I have some question still about the date function.
Question 1: the filter pane that you had created only allows to select one certain date. When I tried to recreate it, I couldn't achieve the same end result. So mine let's users click on multiple days (which leads to question 2), how can I do the same as you did?
Question 2: with my old dashboard, I had a line chart with the three gas types in it. Since we unpivoted the gas types, It only allows me to select the overall gas type in a line chart. I could put a filter pane next to it of course or create some buttons, but do you know a workaround to get it all in one?
Question 3: on my old dashboard I had a date slicer. So I could see what the average price was of a certain month, 7 years ago. Now when I try to do that, all the gas prices seem summarized.
So your solution did help me to create a this week/last week/last month visualisation, but I had to sacrifice my other visualisations.
Is it an option to make 2 data tables? One data table where I have the gas prices as before (not unpivoted, what I had before) and one data table where I have the gas prices unpivoted (your solution)?
Thanks in advance!
@Gjakova let me try to answer:
#1 - Go to format pane and change slicer type to single select
#2 - you should put gas type in the legend and it should work
#3 - as we did in our graph, put avg in a table visual and put gas type in it, it is more flexible and single measure work across all types, I'm sure surely previously your created 3 measures to achieve a result for each gas type.
Hope this help, I'm here to help or answer further questions.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k hi there!
Question #1 and #2 are solved, although I don't understand your solution regarding my third question...
In the sample file you provided you had two matrix tables and three multi-row cards of my gas types prices for each period. The problem is that I alway have to select a certain date, otherwise it shows the SUM, instead of the measurement. And when I select a date, then you can only see one data point in my line chart. Instead of everything from the last 15+ years.
Thanks for the tip! Yeah it is bizarre, we spent a lot of time on just a ,
Anyways cheers!
@Gjakova did you marked mastercalendar table as date table
Would appreciate Kudos 🙂 if my solution helped.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes, it was already selected like that. Does it not show the same error at yours? Because I haven't made any changes yet...
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |