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!
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.
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!
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?
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.
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.
@@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 + ^
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?
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.
A quick question/observation: the Week, Month, and Quarter buckets seem to incorporate all Sales for that bucket regardless of year. So for multiple years you get a 1 year picture, with all results aggregated?
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?
How to fix the sort order for the parameter, its sorting for revenue by default i want it to show month names in order when chosen month and then year etc
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
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)
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.
Thanks Bas this is great. One thing - I can't seem to get the field parameter to pick up the (filtered) number of months from the DATEDIFF. It always shows the DATEDIFF for the entire dataset, not for the one from the filter. Any ideas from anyone would be appreciated.
Hi Walter :) thank you! In the download file for this video there is an easier implementation ( see this short video SMART Date Axis #power #shorts ua-cam.com/users/shortstQYQLRGw3qU?feature=share )
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
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:)
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
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 + ^
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?
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!
When I create my parameters with day, month and quarter to insert on the X axis of a line graph, it accumulates the values in one point and does not work as in the video. What should I do to be able to return the correct list of dates?
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?
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.
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
@@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.
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.
@@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.
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
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
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?
@@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.
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?
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 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.
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
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
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] ) )
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.
Putting the field as the first argument requires you to specify all possible values that you want to distinct from the else. Using True as the first argument gives you now and in future the option to use more complex functions that may or may not return true. In the end it is a matter of optimalisation. If you have different actions for every unique value, your proposed way is optimal (since you do not need to repeat the fieldname). If you have multiple values that require the same action or complex conditions, you should use the approach with true. Some pick the true approach by default (accepting the inefficiency of repeating the field) to achieve consistency in the way they use the switch function. In this example, the use of "months
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!
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)
this man is beyond powerBI.
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.
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!
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.
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
Thank you. Perfect Timing as I am having this issue. Love your solution. I learn from your channel everyday.
You are moving to "GOD LEVEL" in PowerBI. 🖖🖖Thanks for everything you taught.
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!
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?
This is awesome and necessary. Definitely worth revisiting and re-doing with the actual best solution.
Bas, You're the best!
Very helpful and wonderfully presented. I really like the updated version to work with "Between" dates. Thank you.
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.
Amazing!! I'm a fan of your content, keep doing this. Your content is very helpful especially your tricks in the design. Thank you
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.
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
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.
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!
Вау, дуже дякую, корисні підказки, взяв собі "в скриню".
Бажаю тобі мирного неба
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 💕
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?
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.
A quick question/observation: the Week, Month, and Quarter buckets seem to incorporate all Sales for that bucket regardless of year. So for multiple years you get a 1 year picture, with all results aggregated?
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...
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?
another classic from BAS, thank you for sharing
Good morning Bas, thanks for posting the video! Have a great day.
Thanks Tron, you too!
How to fix the sort order for the parameter, its sorting for revenue by default i want it to show month names in order when chosen month and then year etc
This is fantastic! Do you have any suggestion when you have to produce a report in two different languages?! How to have this dynamic?
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
I can see that the min date option is disabled so can you make a video changing the min date as well
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)
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.
Thanks Bas this is great. One thing - I can't seem to get the field parameter to pick up the (filtered) number of months from the DATEDIFF. It always shows the DATEDIFF for the entire dataset, not for the one from the filter. Any ideas from anyone would be appreciated.
Hi Walter :) thank you! In the download file for this video there is an easier implementation ( see this short video SMART Date Axis #power #shorts
ua-cam.com/users/shortstQYQLRGw3qU?feature=share )
@HowtoPowerBI Thank you Bas!
Brilliant demonstration. Thanks for sharing this tutorial
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
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)
excellent tips, I am brazilian.
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
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
I would say you are very talented and patient.
thank you so much 😊
Love this, Bas! I'm going to use this for sure! Thanks!
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 + ^
Just Amazing !! Thanks, thanks, thanks ! Very nice content !!!!
Do you have a video on how to create a week/id column?
ua-cam.com/video/CnkUqUzdgQU/v-deo.html
Thank you!
@@HowtoPowerBI
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?
Fantastic as usual Bas. Thank you so much
My pleasure!
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.
When I create my parameters with day, month and quarter to insert on the X axis of a line graph, it accumulates the values in one point and does not work as in the video. What should I do to be able to return the correct list of dates?
i would use an alternative method see link to short vid in description
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!
Definitely advanced. 😮
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.
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 ;)
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?
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
You are a genius!! I will use it in my next dashboard :)
thank you Marta ! ☺
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
Outstanding 🔥
Thanks 🔥
Just Wow!!
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.
Can you teach us how to use a Dynamic Query Parameter with date filters please?
Very useful... thanks
very clever !! keep up the good work ^^
Thank you! 😊
Wow! It's brilliant!
This is so helpful!
Happy to hear this Steffan :)
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.
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
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
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.
Thanks for sharing ❤
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
Next level 🔥
Omg you are Too good 😃😃😃
Excellent 😀
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.
Thank you 🙏
Great tricks!
Glad you think so Aleh!
awesome Bas
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
You are artist
WOW, just WOW!
Really amazing
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]
)
)
Great vedio
thanks Mohamed!
impressive!
You are the best
Genius logic
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.
Putting the field as the first argument requires you to specify all possible values that you want to distinct from the else.
Using True as the first argument gives you now and in future the option to use more complex functions that may or may not return true.
In the end it is a matter of optimalisation. If you have different actions for every unique value, your proposed way is optimal (since you do not need to repeat the fieldname). If you have multiple values that require the same action or complex conditions, you should use the approach with true.
Some pick the true approach by default (accepting the inefficiency of repeating the field) to achieve consistency in the way they use the switch function.
In this example, the use of "months
Brilliant!
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!
Ctrl shift L?! 😍
gold
This is impossible! 😅😅