SMART AXIS Magic | Let Power BI Choose the OPTIMAL Period

แชร์
ฝัง
  • เผยแพร่เมื่อ 27 ธ.ค. 2024

ความคิดเห็น • 169

  • @businessinsights_AlexRobe
    @businessinsights_AlexRobe 2 ปีที่แล้ว +6

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

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

    • @Picla_Peremohy
      @Picla_Peremohy ปีที่แล้ว +1

      Biscuits with the Bas (Ted Lasso reference)

  • @alicedwonderland7733
    @alicedwonderland7733 2 ปีที่แล้ว +1

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

      Glad to hear that Alice (?!) :) always happy to see your comment on the videos.

  • @nevermore17011990
    @nevermore17011990 7 หลายเดือนก่อน +6

    this man is beyond powerBI.

  • @LosoIAm
    @LosoIAm 9 หลายเดือนก่อน

    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!

  • @paulwyatt8523
    @paulwyatt8523 8 หลายเดือนก่อน

    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
      @HowtoPowerBI  8 หลายเดือนก่อน +1

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

    • @paulwyatt8523
      @paulwyatt8523 8 หลายเดือนก่อน

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

  • @maurocalixto
    @maurocalixto 2 ปีที่แล้ว +6

    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.

  • @damionc
    @damionc 2 ปีที่แล้ว +1

    Thank you. Perfect Timing as I am having this issue. Love your solution. I learn from your channel everyday.

  • @Picla_Peremohy
    @Picla_Peremohy ปีที่แล้ว +1

    Bas, You're the best!
    Very helpful and wonderfully presented. I really like the updated version to work with "Between" dates. Thank you.

  • @jeffbeaty6955
    @jeffbeaty6955 5 หลายเดือนก่อน

    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?

  • @SumanMondal-d2y
    @SumanMondal-d2y 6 หลายเดือนก่อน

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

  • @hansgettheflammenwerfer69420
    @hansgettheflammenwerfer69420 ปีที่แล้ว

    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.

  • @juniorexperience
    @juniorexperience ปีที่แล้ว

    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!

  • @RB26_Aces
    @RB26_Aces 2 ปีที่แล้ว +9

    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
      @jenniferdustin8214 ปีที่แล้ว +1

      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.

  • @aucesarbr
    @aucesarbr 2 ปีที่แล้ว +1

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

      thanks a lot Augusto!!!

    • @MCDMusica
      @MCDMusica 5 หลายเดือนก่อน

      ​@@HowtoPowerBI I agree, you Bas rock, thank you for helping us, and made PBI so easy to understand

  • @peterjohnsarador6194
    @peterjohnsarador6194 2 ปีที่แล้ว +1

    Amazing!! I'm a fan of your content, keep doing this. Your content is very helpful especially your tricks in the design. Thank you

  • @ChrisSmithFW
    @ChrisSmithFW 11 หลายเดือนก่อน

    This is awesome and necessary. Definitely worth revisiting and re-doing with the actual best solution.

  • @fsanfo
    @fsanfo ปีที่แล้ว

    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
      @HowtoPowerBI  ปีที่แล้ว

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

    • @fsanfo
      @fsanfo ปีที่แล้ว

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

  • @bohdanbodnar9280
    @bohdanbodnar9280 ปีที่แล้ว +1

    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

  • @faid2n
    @faid2n 2 ปีที่แล้ว +1

    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?

  • @saared6705
    @saared6705 ปีที่แล้ว +1

    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
      @jeremiasp5829 9 หลายเดือนก่อน

      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
      @saared6705 9 หลายเดือนก่อน

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

  • @sahmedalee
    @sahmedalee 2 ปีที่แล้ว +1

    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
      @HowtoPowerBI  2 ปีที่แล้ว +2

      Glad it was helpful Ahmed!

  • @mlehej4729
    @mlehej4729 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว +1

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

  • @walterstevens8676
    @walterstevens8676 หลายเดือนก่อน

    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?

  • @jaimec4572
    @jaimec4572 2 ปีที่แล้ว +1

    Hi bob i have the question with smart axis how to sort all the calendar to be correct in graph

    • @chiaraventurelli6271
      @chiaraventurelli6271 ปีที่แล้ว

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

  • @walterstevens8676
    @walterstevens8676 หลายเดือนก่อน

    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.

    • @HowtoPowerBI
      @HowtoPowerBI  หลายเดือนก่อน +1

      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
      th-cam.com/users/shortstQYQLRGw3qU?feature=share )

    • @walterstevens8676
      @walterstevens8676 หลายเดือนก่อน

      @HowtoPowerBI Thank you Bas!

  • @francisvilleneuve4791
    @francisvilleneuve4791 2 ปีที่แล้ว +2

    This is fantastic! Do you have any suggestion when you have to produce a report in two different languages?! How to have this dynamic?

  • @mishymoo34
    @mishymoo34 2 ปีที่แล้ว

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

  • @jenniferdustin8214
    @jenniferdustin8214 ปีที่แล้ว

    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?

  • @jenniferdustin8214
    @jenniferdustin8214 ปีที่แล้ว +1

    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?

  • @ApolloSousa-k1m
    @ApolloSousa-k1m 4 หลายเดือนก่อน

    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?

    • @HowtoPowerBI
      @HowtoPowerBI  4 หลายเดือนก่อน

      i would use an alternative method see link to short vid in description

  • @MrSparkefrostie
    @MrSparkefrostie 2 ปีที่แล้ว +6

    That keyboard shorcuts though 🤤

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +3

      lol, makes life easier😄

    • @casingena1985
      @casingena1985 2 ปีที่แล้ว +1

      @@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
      @HowtoPowerBI  2 ปีที่แล้ว +8

      @@casingena1985 th-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 + ^

    • @rajbhor7885
      @rajbhor7885 2 ปีที่แล้ว +1

      @@HowtoPowerBI Thank you so much for shortcuts 💕

  • @domantasdomantavicius6168
    @domantasdomantavicius6168 2 ปีที่แล้ว +1

    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
      @Schilas 2 ปีที่แล้ว +2

      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

  • @chiaraventurelli6271
    @chiaraventurelli6271 ปีที่แล้ว

    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
      @HowtoPowerBI  ปีที่แล้ว

      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)

  • @johanteessen7620
    @johanteessen7620 2 ปีที่แล้ว

    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.

  • @jiteshkumar8228
    @jiteshkumar8228 ปีที่แล้ว

    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

  • @deniskomar2034
    @deniskomar2034 2 ปีที่แล้ว

    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

  • @Олександр-т1г123
    @Олександр-т1г123 2 ปีที่แล้ว +1

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

    • @Picla_Peremohy
      @Picla_Peremohy ปีที่แล้ว

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

  • @marklesterfigueras9977
    @marklesterfigueras9977 ปีที่แล้ว

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

    • @HowtoPowerBI
      @HowtoPowerBI  ปีที่แล้ว

      You can actually do it in an easier way.... see this short video and the file you can download th-cam.com/users/shortstQYQLRGw3qU?feature=share

    • @marklesterfigueras9977
      @marklesterfigueras9977 ปีที่แล้ว

      @@HowtoPowerBI thanks a lot, Bas!

  • @NickPotts21
    @NickPotts21 2 ปีที่แล้ว +1

    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
      @HowtoPowerBI  2 ปีที่แล้ว +2

      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
      @NickPotts21 2 ปีที่แล้ว

      @@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
      @martinbubenheimer6289 2 ปีที่แล้ว +1

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

  • @unnikrishnansanthosh
    @unnikrishnansanthosh 2 ปีที่แล้ว

    another classic from BAS, thank you for sharing

  • @Kingtony1010
    @Kingtony1010 4 หลายเดือนก่อน

    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

  • @abdulrahmanalnajjar1496
    @abdulrahmanalnajjar1496 2 ปีที่แล้ว

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      thank you Abdulrahman! ☺ happy to hear that

  • @ftmagicart
    @ftmagicart 2 ปีที่แล้ว

    Brilliant demonstration. Thanks for sharing this tutorial

  • @scottwilliams6741
    @scottwilliams6741 2 ปีที่แล้ว

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

  • @frankdegooijer99
    @frankdegooijer99 8 หลายเดือนก่อน

    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
      @HowtoPowerBI  8 หลายเดือนก่อน

      Are you using the updated approach? See link in description

    • @HowtoPowerBI
      @HowtoPowerBI  8 หลายเดือนก่อน

      Yes 🙌 dutch

    • @frankdegooijer99
      @frankdegooijer99 8 หลายเดือนก่อน

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

  • @avinashagrawal6956
    @avinashagrawal6956 2 ปีที่แล้ว

    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.

  • @bilbao2468
    @bilbao2468 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว

      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

  • @ankitabansal102
    @ankitabansal102 ปีที่แล้ว

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

  • @soraiacarvalho7213
    @soraiacarvalho7213 2 ปีที่แล้ว

    excellent tips, I am brazilian.

  • @Schilas
    @Schilas 2 ปีที่แล้ว

    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?

  • @РинатХабибрахманов-ш1й
    @РинатХабибрахманов-ш1й 2 ปีที่แล้ว

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

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

  • @jeromestival3210
    @jeromestival3210 2 ปีที่แล้ว

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

  • @jovenalegre9522
    @jovenalegre9522 10 หลายเดือนก่อน +1

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

    • @HowtoPowerBI
      @HowtoPowerBI  10 หลายเดือนก่อน

      th-cam.com/video/CnkUqUzdgQU/w-d-xo.html

    • @jovenalegre9522
      @jovenalegre9522 10 หลายเดือนก่อน

      Thank you!
      @@HowtoPowerBI

  • @Sisi-vp7xs
    @Sisi-vp7xs ปีที่แล้ว

    I would say you are very talented and patient.

  • @PedroCabraldaCamara
    @PedroCabraldaCamara 2 ปีที่แล้ว

    Fantastic as usual Bas. Thank you so much

  • @GaryMillerUK
    @GaryMillerUK 2 ปีที่แล้ว +1

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

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

  • @PeterKontogeorgis
    @PeterKontogeorgis 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว +2

      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
      @HowtoPowerBI  2 ปีที่แล้ว +5

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

    • @WaleIlori
      @WaleIlori ปีที่แล้ว

      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

  • @Tohitoh
    @Tohitoh 2 ปีที่แล้ว

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +2

      th-cam.com/users/shortstQYQLRGw3qU?feature=share here u go :) .. updated solution is in the download (see description)

    • @Tohitoh
      @Tohitoh 2 ปีที่แล้ว +1

      @@HowtoPowerBI TY

  • @egorshiyanov1206
    @egorshiyanov1206 2 ปีที่แล้ว +1

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      curious.. how would you integrate the calculation groups?

    • @egorshiyanov1206
      @egorshiyanov1206 2 ปีที่แล้ว

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

    • @egorshiyanov1206
      @egorshiyanov1206 2 ปีที่แล้ว +1

      Manybe I’ll come up with the demo

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

      @@egorshiyanov1206 looking forward to see it :)

    • @Picla_Peremohy
      @Picla_Peremohy ปีที่แล้ว

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

  • @martacastrillodelgado6475
    @martacastrillodelgado6475 2 ปีที่แล้ว

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

  • @frankwater2568
    @frankwater2568 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว

      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
      @nicolasrousseau2027 2 ปีที่แล้ว +7

      @@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
      @HowtoPowerBI  2 ปีที่แล้ว

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

    • @nicolasrousseau2027
      @nicolasrousseau2027 2 ปีที่แล้ว

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

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

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

  • @mfella8065
    @mfella8065 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว +1

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

  • @2912801821
    @2912801821 7 หลายเดือนก่อน

    Very useful... thanks

  • @abtokarev
    @abtokarev 2 ปีที่แล้ว

    Wow! It's brilliant!

  • @joaopedromadeira7357
    @joaopedromadeira7357 ปีที่แล้ว

    Outstanding 🔥

  • @philoooooo
    @philoooooo 2 ปีที่แล้ว

    very clever !! keep up the good work ^^

  • @Caminodigital360
    @Caminodigital360 2 ปีที่แล้ว

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

  • @steffaneyram2369
    @steffaneyram2369 2 ปีที่แล้ว

    This is so helpful!

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      Happy to hear this Steffan :)

  • @itgamer4143
    @itgamer4143 3 หลายเดือนก่อน

    Definitely advanced. 😮

  • @FRANKWHITE1996
    @FRANKWHITE1996 2 ปีที่แล้ว

    Thanks for sharing ❤

  • @johnskilly
    @johnskilly 2 ปีที่แล้ว

    Great stuff again Bas

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว

      Thanks a lot! ☺

    • @johnskilly
      @johnskilly 2 ปีที่แล้ว

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

  • @luizhenriquekawalkievicz5711
    @luizhenriquekawalkievicz5711 2 ปีที่แล้ว

    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
      @HowtoPowerBI  2 ปีที่แล้ว +1

      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
      @luizhenriquekawalkievicz5711 2 ปีที่แล้ว

      @@HowtoPowerBI Really, thanks for the reply

    • @HowtoPowerBI
      @HowtoPowerBI  2 ปีที่แล้ว +1

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

    • @Picla_Peremohy
      @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]
      )
      )

  • @trustnonor5139
    @trustnonor5139 2 ปีที่แล้ว

    Thank you 🙏

  • @ThepExcel
    @ThepExcel 2 ปีที่แล้ว

    Just Wow!!

  • @sabrial-ozairi8051
    @sabrial-ozairi8051 2 ปีที่แล้ว

    Excellent 😀

  • @rajatbhamare2785
    @rajatbhamare2785 2 ปีที่แล้ว

    Next level 🔥

  • @Datavers
    @Datavers 2 ปีที่แล้ว

    Omg you are Too good 😃😃😃

  • @DranickMann
    @DranickMann 2 ปีที่แล้ว

    Great tricks!

  • @SolidSnake59
    @SolidSnake59 2 ปีที่แล้ว

    WOW, just WOW!

  • @mogarrett3045
    @mogarrett3045 ปีที่แล้ว

    awesome Bas

  • @pmenown
    @pmenown 2 ปีที่แล้ว +3

    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.

    • @eugene__eugene
      @eugene__eugene 4 หลายเดือนก่อน

      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

  • @thenewdataguy
    @thenewdataguy 2 ปีที่แล้ว

    Really amazing

  • @Saif_Ali_Khan_1301
    @Saif_Ali_Khan_1301 ปีที่แล้ว

    You are artist

  • @lotte9324
    @lotte9324 ปีที่แล้ว

    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

  • @Jakobe123
    @Jakobe123 ปีที่แล้ว

    impressive!

  • @motasemfarhat
    @motasemfarhat 2 ปีที่แล้ว

    You are the best

  • @ymohamed3154
    @ymohamed3154 2 ปีที่แล้ว

    Great vedio

  • @jonnyparsons3217
    @jonnyparsons3217 2 ปีที่แล้ว

    Genius logic

  • @totvabe1
    @totvabe1 2 ปีที่แล้ว

    Brilliant!

  • @jacson5950
    @jacson5950 2 ปีที่แล้ว

    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!

  • @user-nu5pi1yx6y
    @user-nu5pi1yx6y 2 ปีที่แล้ว +1

    Ctrl shift L?! 😍

  • @romariodejesusariaszuniga6558
    @romariodejesusariaszuniga6558 ปีที่แล้ว

    gold

  • @rogeriodornellas2115
    @rogeriodornellas2115 3 หลายเดือนก่อน

    This is impossible! 😅😅