SMART AXIS Magic | Let Power BI Choose the OPTIMAL Period
Вставка
- Опубліковано 21 лип 2024
- Here is how to improve UX by creating a smart date axis that automatically picks the optimal calendar period based on the timeframe. In this step-by-step explanation we dive deeper into field parameters, data modeling, and DAX.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks :)
Download file here datatraining.io/powerbi-how-to
*See improved solution in the download files, where you don't need the bridge table and can use a between date slicer
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
01:43 Step 1 - Creating a field parameter with periods
03:23 Step 2 - Creating a helper table
07:17 Step 3 - Creating the relationships
09:14 Step 4 - Applying a TopN filter
10:14 Demo - smart date axis
10:55 Finishing touch - Dynamic title
13:40 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on UA-cam: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX - Наука та технологія
Im moving into a full time reporting role soon and I love these vids. They've helped enormously as I've gone from 0 powerbi knowledge to running the entire reporting function for my company.
I have a long way to go, but I'll be signing up for your training course next month to help me along the way.
Thank you. Perfect Timing as I am having this issue. Love your solution. I learn from your channel everyday.
Bas, You're the best!
Very helpful and wonderfully presented. I really like the updated version to work with "Between" dates. Thank you.
Wow, eye-opening. I am very new to Power BI and I don't get much from this but what this great video has taught me is that it is possible to make the display dynamic. Thank you very much!
this man is beyond powerBI.
Nice video! I think if you leave the calculated table without the relationships, you could simply sync slicers using both dates fields and hide it from the floating slicer. You would be able to get the same result, but much simpler way.
Amazing!! I'm a fan of your content, keep doing this. Your content is very helpful especially your tricks in the design. Thank you
This is awesome and necessary. Definitely worth revisiting and re-doing with the actual best solution.
You are moving to "GOD LEVEL" in PowerBI. 🖖🖖Thanks for everything you taught.
Woah, what an amazing way to use helper's table. I have use similar method but for anchor dates, but using is for dynamic axis, you're truely a genius
Glad it was helpful Ahmed!
Love this, Bas! I'm going to use this for sure! Thanks!
How amazing are you with your ideas of simplifying things... this is how digital development is art👌👍
Thanks ..
I really like it
thank you Abdulrahman! ☺ happy to hear that
Great! Bas, I love your videos!)
And how can I make the same but choose month and year if my range includes two years but I want to show by month
Brilliant demonstration. Thanks for sharing this tutorial
another classic from BAS, thank you for sharing
Thank You Bas, I'm just implementing this into my new models. I learnt so much from this technique. I'd like to add that this works perfectly, as you demonstrated, when not using Calculation Groups or when NOT setting a Semantic model to ' Discourage implicit measures' but on switching to an EXPLICIT MODE, the technique breaks down due to the loss of the ability to select summations, like MAX!
There are two ways around this, the first being to build it in a new model, before either using CGs or switching semantic model to 'discourage implicit measures' - not good, and a false sense of security. The alternative, and best approach, is to first create an explicit measure for the MAX [AutoperiodOrder] column for use in the 'By Value' Filter Field.
I've been spending a lot of time converting great work from you (Mostly you actually) and others in this way; my best practice is always to switch to explicit from the first instance of a model.
😊 indeed always a good idea to go for explicit measures , make sure to check the improved solution as well (see link in description)
@@HowtoPowerBI Nice - thanks, I had not seen that (clearly) in the comments. I should have known you'd be onto such a point. Sorry. Love your content, you've moved me so far forward.
Thanks for all your videos, Bas! They help me every day!
Glad to hear that Alice (?!) :) always happy to see your comment on the videos.
This is fantastic! Do you have any suggestion when you have to produce a report in two different languages?! How to have this dynamic?
Good morning Bas, thanks for posting the video! Have a great day.
Thanks Tron, you too!
Hey,
Ive noticed that while using the smart axix, which is awesome btw, there is no option to use the trend line in the line chart. Weird is that if you setup it before using the parameter its kinda there but just for some of the style of periods. Anyone that could help me with that?
hello, thanks for your channel!
I try to use this case, but if I have a period of more than 1 year, how does it works Bas? for example if I need a data check for 2 years and in a bar chart I would see data by Quarter and year.
( Now I see only four quarters, but a period of more than 1 year
Hi Bus, thank you very much for your videos it’s really helpful
I have one query, that how we can select dynamic and static date on the selection of slicer,
Example I have one slicer having value (year, month,quarter and date ) and second slicer ( dynamic and static)
If I select dynamic from second slicer and month from first slicer then sales should show for 30 days window and if I select static then it should show actual month wise sales
What an awesome simple and fast method. Once again Bas: really helpful! Get yourself some cake n coffe 😃🙏
oh wow! thanks a lot Alex, coffee and cake was delicious :)
Biscuits with the Bas (Ted Lasso reference)
Thanks for the videos Bas, love your work! We can use something like this for title:
SWITCH(
MAX(ORDER),
0, "Sales by DAY",
...
4, "Sales by YEAR"
)
nice addition thx! (also make sure to watch the short vid of this or file for an easier solution / setup )
@@HowtoPowerBI I saw the short vid but my SELECTEDVALUE ( AutoPediod[Order] ) is always BLANK. Can't figure it out yet.
It is Awesome. but while the dates go more than a year the X-Axis is not coming in order. Unable to sort it also since it is from Parameter field. Any solutions Please?
He speaks so well that make we think we can do it so easy.....Thanks for the tips!
thanks a lot Augusto!!!
@@HowtoPowerBI I agree, you Bas rock, thank you for helping us, and made PBI so easy to understand
excellent tips, I am brazilian.
This is great, but could you do it for something like I Hierarchy? Division, Facility, Process, System.... so that if the filter is on the Facility, its broken down by facility? unless only 1 facility is selected, then its by process?
Excellent stuff Bas!
I’m curious, is there an advantage to doing the SWITCH to specify TRUE and then each line has to specify MonthCount = 0,1,2,3,4 ? I typically put the MonthCount where the TRUE is to begin the SWITCH statement and then each line just has the values I’m looking for, so my statement would begin with SWITCH( MonthCount, 1, “Sales by Date”, 2, “Sales by Week”…..etc
For switch to work that way you need a static value, if you want to use a formula (aka less than or greater than) you have to use the TRUE method. So for it to work in this case, you'd need an entry for if monthcount is equal to everything from 0 to 36.
Good day
I ave a problem that i am struggling with. I am working with time series data containing weekly prices of beef. The problem is that the new prices for each week comes out on a Friday. The reports that i make goes out on a Monday. My question is i would like to forecast a single value in a card format that use seasonality in the function to forecast a single value for the coming week. This value should not just reflect the previous years value but n true foretasted value like it can be done in excel.
I hope you can help me on this matter.
This is kind of videos are why I am subscribed to you! Amazing and thank you for that!.
I don’t want to brag but… I found a solution and got it working with “between” slicer without the need for refreshing:)
Thx a lot!! 😁 yea also figured it out after uploading (see updated link)
Just Amazing !! Thanks, thanks, thanks ! Very nice content !!!!
I can see that the min date option is disabled so can you make a video changing the min date as well
You are a genius!! I will use it in my next dashboard :)
thank you Marta ! ☺
At around 7:20 where you create the relationship between dimDate and DateToPeriodBridge, I get an error stating, "A circular dependency was detected...". What step might I have missed that would have prevented this?
Fantastic as usual Bas. Thank you so much
My pleasure!
Can you teach us how to use a Dynamic Query Parameter with date filters please?
That keyboard shorcuts though 🤤
lol, makes life easier😄
@@HowtoPowerBI Was it Carl Shift L? That’s a game changer. It might be worth a video on shortcuts if there are any other gems.
@@casingena1985 ua-cam.com/users/shortsI5HSerFTSDI
DAX Shortcuts:
Zooming: Ctrl + and Ctrl -
Intellisense: Ctrl + Spacebar
Insert New Line:
Insert line below (with Indentation): Shift + Enter
Insert line below (without Indentation): Alt + Enter
Insert line above: Ctrl + Shift + Enter
Indent:
Indent line: Ctrl + ] / [ or TAB / Shift + Tab *
Move / Copy Line:
Move line up / down: Alt + Up arrow key / Down arrow key
Copy line up / down: Shift + Alt + Up arrow key / Down arrow key
Navigate / Select:
Select all occurrences of current word: Ctrl + F2 or Ctrl + Shift + L
Select next occurrence of current word: Ctrl + D
Jump to matching bracket: Ctrl + Shift + \ *
Go to row: Ctrl + G
Insert cursor: Alt + Click
Select current line: Ctrl + I
Delete entire row: Ctrl + Shift + K
Comment:
Comment a line: Ctrl + / or Ctrl + K + C
Uncomment a line: Ctrl + / or Ctrl + K + U
Multi-Line comment: Ctrl + Shift + A
*On Keyboard with German Layout:
Indent line: Ctrl + ß / ´
Jump to matching bracket: Ctrl + Shift + ^
@@HowtoPowerBI Thank you so much for shortcuts 💕
Very useful... thanks
I would say you are very talented and patient.
thank you so much 😊
ok, this works, but not really? Each time my X Axis changes, the SORT resets to the data that didn't move, and I have to resort it. Is there a way to stop that from happening? A way to force my date table info to always be the sort?
Awesome work Bas. Even more so I love that you mentioned the limitation. All along I was trying to figure out how this could be done using a between slicer. The obvious solution which came to mind was to count the selected dates in the all selected filter context but this dynamic element can’t be used in the table definition right? If I get a chance will spend some time brainstorming.
thats correct, went down that path already - but it will then not adjust dynamically based on the slicer selection.. you would need to recalculate the table / refresh
Just updated the download file with a different approach from @Nicolas Rousseau - easier and allows for a between slicer
This may explain why I've used the slicer with the between dates approach and cant get it to recognize or dynamically update the date difference. Surely there has to be a way around this?@@HowtoPowerBI
Вау, дуже дякую, корисні підказки, взяв собі "в скриню".
Бажаю тобі мирного неба
Hi bob i have the question with smart axis how to sort all the calendar to be correct in graph
hi @HowtoPowerBi i have the same problem in my graph when i change the selection of parameter, the sort of the graph change...
Wow! It's brilliant!
Hi Bus, great idea! I tried to reproduce the parameter, but I have a problem in sorting the x-axis of the graph... when I change the parameter selection, the sorting of the graph changes every time. Do you have any idea how to solve it? tnks :)
you got the sort by columns set up for the fields on the x axis ? Also make sure to use the technique from the example file (explanation in the short video of this one - is a bit easier to set up)
I'm actually having an issue when creating the bridge table, in the formula Datediff(MIN(TABLE[DATE], here in the second argument it is'nt allowing me to use a table or any column when I type date or the date table even though I forced and used only '[date]', MONTH)...
The table is created but something is wrong. First is the year is in a incorrect format, example: 01/01/0201 00:00:00, 02/01/0201 00:00:00, 03/01/0201 00:00:00.. etc (0201 was suppose to be 2021).
Of course it won't work, but when I try to make a relationship between those table it return a weird error stating: A circular dependency was detected...
Does anybody else has been facing this same issue?
Thanks!
very clever !! keep up the good work ^^
Thank you! 😊
Thanks for sharing ❤
Good Morning Bass. You make it so nicely. Just one question. I think the dates table only has one year, correct? I take it that if it had more than one year it would show the numbers of years in the parameter?
yes then it would show more year :) .. if you would still want a month or quarter breakdown then I would return monthyear and quarteryear instead
This is so helpful!
Happy to hear this Steffan :)
Thank you 🙏
You are a genius 😉… This is exactly what I’m looking for but for a matrix table. Is it possible? Also, I’m not using a version of PBI with Field Parameters available but I don’t think this is a problem right?
thanks a lot! would work the same for a matrix. You will need field parameters though
Excellent 😀
When trying to use field parameters, my X-Axis sorting gets messed up and is always sorting based on values Ascending or descending) and not according to the timeline. I thought this is a bug as this feature is still in preview, but I can see it works fine for you, so it is not a bug.
Any idea how to get this axis sorting to follow timeline and not the values?
Hey,
I had the similar problem and best solution was to create interger sort column for each period type and use it as the sort by column. Once you select how it should be sorted in the visual, it stayed that even after switching to another parameter and back
Hi Bas ! Thank you very much.
Does this technique works with "between" slicer and not just "before" slicer ?
And I have an auxilliary question : How to have the right limit of the slicer always on the extrem right value when an user open the report ? When my values are refreshed, the slicer stay stuck on the last actualisation value (for a "between" date slicer, but it must me the same for a "before" date slicer.
Best regards from France
Hi saared,
Did you find any method to work with 'date between' slicer? I need to replicate this dinamically with min and max dates, creating a calculated table does not work.
@@jeremiasp5829 Hi Jeremi ! I solved my problem with a very simple solution, but I dont know if it is what you are searching for : When you are ready to publish your reporting on powerBi web, look over your reporting and all your visuals, filters and slicers and click on the "rubber" to clean every filter. For a "Between" Slicer, I made the mistake to push the limits to the extreme sides, but when dates changed dynamicaly, the end of the range stayed on the last date it was when I pushed it. The best way to make the slicer always stay on the extreme sides is to just erase the filter by clicking on the rubber icon on the top right side.
Omg you are Too good 😃😃😃
It's fantastic!
Bass, where do you get ideas for your videos?😊
thanks you so much! I got some questions that asked whether this was possible and saw it in an app ;)
awesome Bas
Hi, i have a dashboard creates for supplier scorecard in power bi..i want to export it in pdf and save it to a folder on basis of supplier..its means pdf to be save with that supplier name..total supplier are 500, so 500 pdf files.
Please respond
Next level 🔥
Great tricks!
Glad you think so Aleh!
Really amazing
Hi Bas,
I am getting an error of 'circular dependency' upon changing the cardinality to one to many (for dimdate dateperiodbridge relationship)
You can actually do it in an easier way.... see this short video and the file you can download ua-cam.com/users/shortstQYQLRGw3qU?feature=share
@@HowtoPowerBI thanks a lot, Bas!
Didn't know that Ctrl+Shift+L trick to select all similar lines in the code!
maybe you like a few of the other ones as well
DAX Shortcuts:
Zooming: Ctrl + and Ctrl -
Intellisense: Ctrl + Spacebar
Insert New Line:
Insert line below (with Indentation): Shift + Enter
Insert line below (without Indentation): Alt + Enter
Insert line above: Ctrl + Shift + Enter
Indent:
Indent line: Ctrl + ] / [ or TAB / Shift + Tab *
Move / Copy Line:
Move line up / down: Alt + Up arrow key / Down arrow key
Copy line up / down: Shift + Alt + Up arrow key / Down arrow key
Navigate / Select:
Select all occurrences of current word: Ctrl + F2 or Ctrl + Shift + L
Select next occurrence of current word: Ctrl + D
Jump to matching bracket: Ctrl + Shift + \ *
Go to row: Ctrl + G
Insert cursor: Alt + Click
Select current line: Ctrl + I
Delete entire row: Ctrl + Shift + K
Comment:
Comment a line: Ctrl + / or Ctrl + K + C
Uncomment a line: Ctrl + / or Ctrl + K + U
Multi-Line comment: Ctrl + Shift + A
*On Keyboard with German Layout:
Indent line: Ctrl + ß / ´
Jump to matching bracket: Ctrl + Shift + ^
Outstanding 🔥
Thanks 🔥
impressive!
Great vedio
thanks Mohamed!
I received a circular dependency warning when attempting to create a relationship between the dimension date table and the helper bridge table. I believe it’s because the variable references date. How did you get around this?
For the helper table do not refer to dimdate[date] but rebuild the date column like with calendarauto.. in the download file you find an updated approach where u dont need the helper table (and also works with between slicer)
@@HowtoPowerBI wonderful! I’ll check that out. Thank you for replying and for this great trick. I’m building a customer report right now and this might just wow them!
I do in general prefer to build such helper tables in Power Query. This will avoid circular dependency issues under any circumstances.
WOW, just WOW!
Brilliant!
You are the best
Great stuff again Bas
Thanks a lot! ☺
@@HowtoPowerBI Any chance on a video (or maybe you already have done one) on how to show a target against the MTD value for multiple countries in 1 chart? This is to measure pacing. Thanks again for all the great knowledge you are passing on.
I think you can combine this technique with usage of calculation groups to make it more dynamic
curious.. how would you integrate the calculation groups?
I’ll think on a solution
2 ways:
1. Complex and huge code
2. Call group with that code on report/visual level
Manybe I’ll come up with the demo
@@egorshiyanov1206 looking forward to see it :)
@@egorshiyanov1206 Do you have a demo to share? I would love to see it.
You are artist
Fantastic as always. Tried to replicate your solution, but i've noticed that you are using a fixed start date. If we have several years on the calendar, it will considerate all the period, and not only the dynamic in between dates from slicer. How to bypass calendarauto(), to only consider the start of date filte? Thanks Bas!
it only works with a before date slicer (not between). so we base our logic from a certain start date. You could adjust the starting point / logic on refresh - not on adjusting the slicer unfortunately
@@HowtoPowerBI You can. Just use this kind of measure for the top 1.
Flag =
var _MonthCount = COUNT('Calendar'[Date])/30
var _AutoPeriod = SELECTEDVALUE('AutoPeriod'[AutoPeriod Order])
return SWITCH(TRUE,
_MonthCount 1 && _MonthCount 3 && _MonthCount 6 && _MonthCount 24 && _AutoPeriod = 4, 1,
0
)
You don't even need the bridge.
And since it's a measure, you could even add some what if parameter in the mix to change scopes.
@@nicolasrousseau2027 ah genius! I've updated the download file with your suggestion
@@HowtoPowerBI the only problem I have is the sort order...
@@nicolasrousseau2027 for which column doesnt it sort correctly? I've had some similar approaches where I ran into sorting problems as well (for the weeknr). When I put a W in front of it and sort it by the weeknr it did work.
Just Wow!!
I suppose it would be easier to create the ppt background with the boxes first to use the ppt alignment functionality rather than bother with the excel and complex pixel calculations
Cool, but it works only for static min date, how to do it when the first date can change?
ua-cam.com/users/shortstQYQLRGw3qU?feature=share here u go :) .. updated solution is in the download (see description)
@@HowtoPowerBI TY
Do you have a video on how to create a week/id column?
ua-cam.com/video/CnkUqUzdgQU/v-deo.html
Thank you!
@@HowtoPowerBI
Genius logic
Hello Bas. I assume you're Dutch (like me). I'm really grateful for solution. However, my x-axis's are not ordered the right way. When years are shown, the run from 1997, 1995, 1996, 1998. The same goes for the months. How can I fix this? Frank
Are you using the updated approach? See link in description
Yes 🙌 dutch
@@HowtoPowerBI I used your Download 2,pibx. The thing is that until MonthCount = 24, no sorting on the X-axis is done and thus the days, weeks, months and quarters are shown in the correct order. I added more rows in the Sales table, so the dates go from 1-1-2020 to 31-12-2023. When MonthCount reaches 24 (2 years), the visual starts filtering om the Total Sales and thus shows the incorrect order of years. When I manually change the sorting to Year, the years on the X-axis are shown in the correct order, but when I drag the slider back to show e.g. the months and then drag it again to show the years, the sorting is done for Years again. I don't know how to fix this. In your pibx this is not a problem, because there is only one year of data.
Why does everyone always put true as the first parameter in a switch statement instead of just putting the field you are looking up the values for. It's a lot less effort to code.
the idea is incredible and very good, however This did not work for me, my table the year starts in 2006, so when the filter is applied for 2022 it does not visualize as it should, the order numbers for 2022 are all 4(Year) and not relatively according to the month.
thats the downside that I mentioned.. You could adjust the logic so the starting point 2022 - but it will only update on refresh not a change in slicer selection
@@HowtoPowerBI Really, thanks for the reply
Just implemented a suggestion from @Nicolas Rousseau where you can achieve this (also easier to implement) .. see updated download file
Have you considered using something like this for creating your date table?
Calendar-Base = CALENDAR(
MINX('dim_Incident_Level_Details',
'dim_Incident_Level_Details'[Start DateTime]),
MAXX('dim_Incident_Level_Details',
'dim_Incident_Level_Details'[Start DateTime]
)
)
Ok, it's a good trick, but if you choose just november for example, the chart will appear as a quarter not in days!
That table DateToPeriodBridge is static. If you choose another year the chart will be crashed probably with a year dot!
gold
Ctrl shift L?! 😍