this man is beyond powerBI.

You are moving to "GOD LEVEL" in PowerBI. 🖖🖖Thanks for everything you taught.

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.

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.

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!

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.

Love this, Bas! I'm going to use this for sure! Thanks!

Brilliant demonstration. Thanks for sharing this tutorial

He speaks so well that make we think we can do it so easy.....Thanks for the tips!

@HowtoPowerBI

Жыл бұрын

thanks a lot Augusto!!!

Thanks for all your videos, Bas! They help me every day!

@HowtoPowerBI

Жыл бұрын

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?

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

@HowtoPowerBI

Жыл бұрын

Glad it was helpful Ahmed!

another classic from BAS, thank you for sharing

How amazing are you with your ideas of simplifying things... this is how digital development is art👌👍 Thanks .. I really like it

@HowtoPowerBI

Жыл бұрын

thank you Abdulrahman! ☺ happy to hear that

What an awesome simple and fast method. Once again Bas: really helpful! Get yourself some cake n coffe 😃🙏

@HowtoPowerBI

Жыл бұрын

oh wow! thanks a lot Alex, coffee and cake was delicious :)

@Picla_Peremohy

Жыл бұрын

Biscuits with the Bas (Ted Lasso reference)

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

Good morning Bas, thanks for posting the video! Have a great day.

@HowtoPowerBI

Жыл бұрын

Thanks Tron, you too!

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

2 ай бұрын

😊 indeed always a good idea to go for explicit measures , make sure to check the improved solution as well (see link in description)

@paulwyatt8523

2 ай бұрын

@@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.

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

@jenniferdustin8214

7 ай бұрын

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.

That keyboard shorcuts though 🤤

@HowtoPowerBI

Жыл бұрын

lol, makes life easier😄

@casingena1985

Жыл бұрын

@@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.

@HowtoPowerBI

Жыл бұрын

@@casingena1985 kzread.infoI5HSerFTSDI 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 + ^

@rajbhor7885

Жыл бұрын

@@HowtoPowerBI Thank you so much for shortcuts 💕

Just Amazing !! Thanks, thanks, thanks ! Very nice content !!!!

Fantastic as usual Bas. Thank you so much

@HowtoPowerBI

Жыл бұрын

My pleasure!

excellent tips, I am brazilian.

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

Very useful... thanks

Thank you 🙏

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.

@HowtoPowerBI

Жыл бұрын

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

@HowtoPowerBI

Жыл бұрын

Just updated the download file with a different approach from @Nicolas Rousseau - easier and allows for a between slicer

@user-ul6mq9ut5i

10 ай бұрын

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 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

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" )

@HowtoPowerBI

8 ай бұрын

nice addition thx! (also make sure to watch the short vid of this or file for an easier solution / setup )

@fsanfo

8 ай бұрын

@@HowtoPowerBI I saw the short vid but my SELECTEDVALUE ( AutoPediod[Order] ) is always BLANK. Can't figure it out yet.

Thanks for sharing ❤

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?

Wow! It's brilliant!

You are a genius!! I will use it in my next dashboard :)

@HowtoPowerBI

Жыл бұрын

thank you Marta ! ☺

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

Жыл бұрын

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

I would say you are very talented and patient.

@HowtoPowerBI

8 ай бұрын

thank you so much 😊

This is so helpful!

@HowtoPowerBI

Жыл бұрын

Happy to hear this Steffan :)

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 bob i have the question with smart axis how to sort all the calendar to be correct in graph

@chiaraventurelli6271

8 ай бұрын

hi @HowtoPowerBi i have the same problem in my graph when i change the selection of parameter, the sort of the graph change...

Just Wow!!

It's fantastic! Bass, where do you get ideas for your videos?😊

@HowtoPowerBI

Жыл бұрын

thanks you so much! I got some questions that asked whether this was possible and saw it in an app ;)

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?

very clever !! keep up the good work ^^

@HowtoPowerBI

Жыл бұрын

Thank you! 😊

Can you teach us how to use a Dynamic Query Parameter with date filters please?

I can see that the min date option is disabled so can you make a video changing the min date as well

Next level 🔥

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?

@HowtoPowerBI

Жыл бұрын

thanks a lot! would work the same for a matrix. You will need field parameters though

Excellent 😀

Really amazing

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.

You are the best

WOW, just WOW!

Omg you are Too good 😃😃😃

Brilliant!

Вау, дуже дякую, корисні підказки, взяв собі "в скриню".

@Picla_Peremohy

Жыл бұрын

Бажаю тобі мирного неба

Great tricks!

@HowtoPowerBI

Жыл бұрын

Glad you think so Aleh!

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

@jeremiasp5829

3 ай бұрын

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.

@saared6705

3 ай бұрын

@@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.

I think you can combine this technique with usage of calculation groups to make it more dynamic

@HowtoPowerBI

Жыл бұрын

curious.. how would you integrate the calculation groups?

@egorshiyanov1206

Жыл бұрын

I’ll think on a solution 2 ways: 1. Complex and huge code 2. Call group with that code on report/visual level

@egorshiyanov1206

Жыл бұрын

Manybe I’ll come up with the demo

@HowtoPowerBI

Жыл бұрын

@@egorshiyanov1206 looking forward to see it :)

@Picla_Peremohy

Жыл бұрын

@@egorshiyanov1206 Do you have a demo to share? I would love to see it.

Didn't know that Ctrl+Shift+L trick to select all similar lines in the code!

@HowtoPowerBI

Жыл бұрын

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 + ^

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:)

@HowtoPowerBI

Жыл бұрын

Thx a lot!! 😁 yea also figured it out after uploading (see updated link)

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 :)

@HowtoPowerBI

8 ай бұрын

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)

impressive!

You are artist

Genius logic

awesome Bas

Outstanding 🔥

@HowtoPowerBI

10 ай бұрын

Thanks 🔥

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?

@Schilas

Жыл бұрын

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 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?

@HowtoPowerBI

Жыл бұрын

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)

@NickPotts21

Жыл бұрын

@@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!

@martinbubenheimer6289

Жыл бұрын

I do in general prefer to build such helper tables in Power Query. This will avoid circular dependency issues under any circumstances.

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!

@HowtoPowerBI

Жыл бұрын

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

@nicolasrousseau2027

Жыл бұрын

@@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.

@HowtoPowerBI

Жыл бұрын

@@nicolasrousseau2027 ah genius! I've updated the download file with your suggestion

@nicolasrousseau2027

Жыл бұрын

@@HowtoPowerBI the only problem I have is the sort order...

@HowtoPowerBI

Жыл бұрын

@@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.

Do you have a video on how to create a week/id column?

@HowtoPowerBI

4 ай бұрын

kzread.info/dash/bejne/daKft9OOqsfNgbg.html

@jovenalegre9522

4 ай бұрын

Thank you! @@HowtoPowerBI

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!

Great vedio

@HowtoPowerBI

Жыл бұрын

thanks Mohamed!

Great stuff again Bas

@HowtoPowerBI

Жыл бұрын

Thanks a lot! ☺

@johnskilly

Жыл бұрын

@@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.

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?

Hi Bas, I am getting an error of 'circular dependency' upon changing the cardinality to one to many (for dimdate dateperiodbridge relationship)

@HowtoPowerBI

Жыл бұрын

You can actually do it in an easier way.... see this short video and the file you can download kzread.infotQYQLRGw3qU?feature=share

@marklesterfigueras9977

Жыл бұрын

@@HowtoPowerBI thanks a lot, 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.

@avinashagrawal6956

Жыл бұрын

Please respond

Cool, but it works only for static min date, how to do it when the first date can change?

@HowtoPowerBI

Жыл бұрын

kzread.infotQYQLRGw3qU?feature=share here u go :) .. updated solution is in the download (see description)

@Tohitoh

Жыл бұрын

@@HowtoPowerBI TY

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

gold

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

2 ай бұрын

Are you using the updated approach? See link in description

@HowtoPowerBI

2 ай бұрын

Yes 🙌 dutch

@frankdegooijer99

2 ай бұрын

@@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.

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.

@HowtoPowerBI

Жыл бұрын

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

@luizhenriquekawalkievicz5711

Жыл бұрын

@@HowtoPowerBI Really, thanks for the reply

@HowtoPowerBI

Жыл бұрын

Just implemented a suggestion from @Nicolas Rousseau where you can achieve this (also easier to implement) .. see updated download file

@Picla_Peremohy

Жыл бұрын

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.

Ctrl shift L?! 😍

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!