CODEWITHSUNDEEP
powerbidaxdashboard
Pouyan Nikzad
Pouyan Nikzad

Reputation: 19

How can I have dynamic axis which is responsive on slicer value, without using bridge table, use just DAX

Dashboard Image

Hi everyone, I have a chart which I need that the x value (Axis) changes by changing the slicer value (this slicer is the yellow one that has all the Dims (x values)) and it comes from DimList table. For example, at the moment the chart is totalfreight by custid, but I need if I check the empid from the yellow slicer the chart value changed to totalfreight by empid. This happens for all of the slicer values.

But I don't like bridge table or any method that has a bad effect on performance because the FactTable has a billion rows and I modeled it in SSAS, tabular model and has a live connection. Thanks in advance.

Upvotes: 0

Views: 2901

Answers (1)

Dan
Dan

Reputation: 10690

I assume you're referring to the approach that uses a bridge table described in this article. I agree that you may face some performance problems given the amount of data in your model.

First of all, you should try to see if there's some other Power BI frontend functionality that you can use directly in the report, without changing your data model. Perhaps you can use Power BI bookmarks, links or maybe a custom visual?

If not, there's another approach you can use in the data model, that does not rely on bridge tables. Disclaimer: I haven't tested this - there could be other performance issues involved.

  1. Construct a new dimension table with all the members from your individual dimensions. Ie. create a union of all EmpIDs, CustIDs, etc. Make sure you indicate the type of ID in a separate column. The table should look like this:

    DimensionId MemberId
categoryid  1
categoryid  2
categoryid  3
custid      1
custid      2
custid      3
...

    Let's name this table 'All Dimensions'. The table should not have any relationships to other tables (this is similar to the Parameter Table pattern.

  2. Change your measures to apply a virtual relationship whenever something is selected on the 'All Dimensions' table, to properly filter the fact table:

    SUM('factSale'[Freight])

    would become:

    SWITCH(
    SELECTEDVALUE('All Dimensions'[DimensionId]),
    "categoryid", CALCULATE(SUM('factSale'[Freight]), 
        KEEPFILTERS(TREATAS(VALUES('All Dimensions'[MemberId]), 'factSale'[CategoryId])),
    "custid", CALCULATE(SUM('factSale'[Freight]), 
        KEEPFILTERS(TREATAS(VALUES('All Dimensions'[MemberId]), 'factSale'[CustId])),
    "empid", CALCULATE(SUM('factSale'[Freight]), 
        KEEPFILTERS(TREATAS(VALUES('All Dimensions'[MemberId]), 'factSale'[EmpId])),
    // ... etc. for all dimensions ...
    , // Fallback, when nothing is selected on 'All Dimensions'
    IF(NOT ISFILTERED('All Dimensions'[MemberId]), 
        SUM('factSale'[Freight])
    )
)
    1. Put the [DimensionId] column into a slicer and use the [MemberId] column as the axis on your bar chart. Note that the chart will not show anything unless exactly one item has been filtered on the [DimensionId] slicer.

Explanation: The SWITCH statement determines if any selection has been made on the [DimensionId] column of the 'All Dimensions' table. In that case, a filter is applied to the fact table, depending on which dimension has been selected, using the TREATAS function. We're using KEEPFILTERS to make sure that any existing filters made directly on the individual dimensions are kept as-is.

In case no selection has been made on the [DimensionId] column, we want to fall back to the standard measure SUM('factSale'[Freight]) but since we don't want to repeat this measure for all items on the [MemberId] column, we use IF(NOT ISFILTERED( ... to make sure that we return only a blank value, if [MemberId] is currently used on the chart axis.

Upvotes: 1

Related Questions