Logo
banner

Blogs

Power BI > Dynamic Scales & Values

, August 31, 2019 3072 Views

Hello DAX-sters!

We have already created a report where we can choose what data we can show – dynamically. You can refer to the blog here.

How about we get the freedom to choose even how the data can be shown, i.e. weekly, monthly, quarterly, or yearly?

Every so often we need the availability of a custom visual of a bar chart or line chart which gives us an option to whether plot the chart weekly, monthly, quarterly, or yearly. Unfortunately, there are no custom visuals available (as per my search) neither does Power BI provide us with any alternative visuals.

I have come up with a trick which not only lets you choose how to show your data on X-axis i.e. either weekly, monthly, quarterly, or yearly but also lets you choose which data to show on Y-axis.

Please note that this approach might add a little bit more data to your model. If your data is already exceeding the data limit, you need to think about this approach.

Let’s create a report which has a slicer that lets you choose whether to show data weekly, monthly, quarterly, or
yearly. Also, it lets you choose which data to show(here, Sales and Order) as shown below:



For illustration purposes, we’ll have data on Sales and Order over 2 years.
Let’s start with building some supporting tables.
For the Sales Data, we have a Fact table – FTSales.
And then, we will have the Dates table(DTDates), which can be created by the following DAX formula:

Date:
CALENDAR(DATE(2015,01,01),DATE(2016,12,31))

The table is also required to have the following calculated columns with their corresponding formulae:

Year = YEAR(DTDates[Date])
Month = DTDates[Year]&" "&FORMAT(DTDates[Date],"MMMM")
Quarter = DTDates[Year]&" " & CONCATENATE("Q",ROUNDUP(MONTH(DTDates[Date])/3,0))
Week = DTDates[Year]&" "&CONCATENATE("Week ",RIGHT(CONCATENATE("0",WEEKNUM(DTDates[Date])),2))

DTDates table should look like this:


For total Sales and total Order, let’s create basic measures which we would need for plotting:

Total Order = SUM('FTSales'[Order])
Total Sales = SUM('FTSales'[Sales])

With this, we have a basic structure with the Fact table and Date table ready for use. However, we will need to add more tables for this technique to work. Let’s see why we need them & how to build them.

To pick the values for the Slicer, we need to have two dimension tables as in the below images:

For VALUE slicer, we need to have a table, let’s name it ‘DTDynamicSlicer’ which has the following items:



Once done, we would now need a table which bridges the gap between Dimension tables and Fact table.
Hence, to bridge the gap, we need to create a table(let’s name it DateSlicer Table) with a value of each week with the word ‘Week’, value of each quarter with the word ‘Quarter’, same for monthly and yearly.

Hence, here we have data of two years, so records with word ‘Week’ will be (52+52), records with word ‘Quarter’ will be (4 + 4), records with word ‘Month’ will be (12+12) and records with word ‘Year’ will be (1+1).

It can be created using the following DAX formula:

DateSlicer =
UNION (
    SUMMARIZE ( DTDates, DTDates[Week], "Frequency", "Week" ),
    SUMMARIZE ( DTDates, DTDates[Month], "Frequency", "Month" ),
    SUMMARIZE ( DTDates, DTDates[Quarter], "Frequency", "Quarter" ),
    SUMMARIZE ( DTDates, DTDates[Year], "Frequency", "Year" )
)

Let’s rename the column ‘Week’ of DateSlicer table as ‘Value’.

The table would somewhat look like this depending on the data:

      

Now, we need to create relationships between Dateslicer table and FTSales table.
Now, we need to create a measure that calculates Total Order when selected ‘Order’ and Total Sales when selected ‘Sales’.

Dynamic Values Measure =
IF (
    HASONEVALUE ( DTDynamicSlicer[Description] )
        && SELECTEDVALUE ( DTDynamicSlicer[Description] ) = "Order",
    [Total Order],
    IF (
        HASONEVALUE ( DTDynamicSlicer[Description] )
            && SELECTEDVALUE ( DTDynamicSlicer[Description] ) = "Sales",
        [Total Sales]
    )
)

Also, we need to create a measure that calculates weekly, quarterly, monthly and yearly as and when selected.

DynamicFequencyMeasure =
IF (
    HASONEVALUE ( DateSlicer[Frequency] )
        && SELECTEDVALUE ( DateSlicer[Frequency] ) = "Week",
    CALCULATE (
        [Dynamic Values Measures],
        USERELATIONSHIP ( DateSlicer[Value], DTDates[Week] )
    ),
    IF (
        HASONEVALUE ( DateSlicer[Frequency] )
            && SELECTEDVALUE ( DateSlicer[Frequency] ) = "Month",
        CALCULATE (
            [Dynamic Values Measures],
            USERELATIONSHIP ( DateSlicer[Value], DTDates[Month] )
        ),
        IF (
            HASONEVALUE ( DateSlicer[Frequency] )
                && SELECTEDVALUE ( DateSlicer[Frequency] ) = "Quarter",
            CALCULATE (
                [Dynamic Values Measures],
                USERELATIONSHIP ( DateSlicer[Value], DTDates[Quarter] )
            ),
            IF (
                HASONEVALUE ( DateSlicer[Frequency] )
                    && SELECTEDVALUE ( DateSlicer[Frequency] ) = "Year",
                CALCULATE (
                    [Dynamic Values Measures],
                    USERELATIONSHIP ( DateSlicer[Value], DTDates[Year] )
                )
            )
        )
    )
)

Add slicer for the month/quarter/week/year and plot ‘Frequency’ from the ‘DateSlicer’ table.


and add another slicer for choosing whether to show Sales data or Order data, which will be plotted from ‘DTDynamicSlicer’.


For the main visual, Axis will have ‘Value’ column from DateSlicer table and Value will have our created measure ‘DynamicFrequencyMeasure’.

Plot the visual as follows:


Let us understand how this works,

Suppose, the user selects ‘Week’ in date slicer and ‘Order’ in another slicer.
First, it will calculate DynamicFrequencyMeasure which in turn calculates DynamicValuesMeasure which calculates [Total Order] using the relationship between DTDates[Week] and DateSlicer[Value].

You can thus create dynamic visual with dynamic X-axis as well as dynamic Y-axis with any dynamic values you want with just the power of DAX.

Happy DAX-ting!

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

12 responses to “Power BI > Dynamic Scales & Values”

  1. Kade Soprano says:

    I cannot get this to work. My main visual only ever shows the measure for the month. If I select any other frequency, my visual is blank. What could be the reason for this?

  2. Kade Soprano says:

    Actually, it’s working differently now. I don’t remember what I actually changed, but the x axis (the dates) do not change based on which frequency selection I choose (week, month, quarter, or year). Instead, all of the different frequencies show and depending on the selection I choose, only the corresponding ones are highlighted in the bar chart. For example, if I click “Quarter” in my frequency slicer, the bar chart visual just highlights the data corresponding to the quarters in the years, but all of the others formats are still visible, so I can see 2018, 2019 for my years, 2018 Week 1, 2018 Week 2, etc for my weeks, and so on and so forth.

    However, when I use the dates as my column headings in a matrix, it works as expected. Only the date formats corresponding to the selection of the slicer show.

    How can I get the x-axis to only show the date formats corresponding to my frequency selection?

    • Kade Soprano says:

      I have figured out the issue. I needed to edit the interactions between the slicer and my bar chart to filter by it instead of highlight by it.

      • Kara Ko says:

        Hi Kade! I’m encountering the same issue. I am not able to see anything on quarterly and yearly. Can you help me? I only see monthly.

        Thank you in advance!

        • Kade Soprano says:

          Did you set up your relationships between the Date table and the Time Selection table correctly?

          • TS says:

            What should be the relationship between DTDates and DateSlicer tables ? Can I get a snap of the relationship ?

          • Admin says:

            The snapshot of the relationship is in the blog just above the measures.
            Please let me know if you cannot see it.

        • Kade Soprano says:

          Also, make sure that your “Week”, “Month”, “Quarter” and “Year” columns are of type Text. The “Value” column in the time selection table also needs to be of type Text. This is probably why you don’t see anything. If the types are mismatched, then Power BI filters everything out because nothing matches because they are of different data types.

  3. Kade Soprano says:

    How can you get the dates to show up sorted on the visuals? Seems rather difficult to sort the ‘Value’ column of your ‘DateSlicer’ table since it has groupings. I don’t see any sort column connected to it and being that the ‘Value’ column is the one being shown in the visual on the x-axis, you cannot rely on your sorted DTDates table, can you?

    At least in my case, my time period values are not showing sorted even though they are connected to my date table and my date table columns are all sorted correctly.

    How can I fix this?

    • Admin says:

      Hello,
      You can create a column for SortOrder and then have your values sorted by the SortOrder column.
      Please let me know if you aren’t able to.

Leave a Reply

Your email address will not be published. Required fields are marked *