Dynamic text column switch for aggregation

[victor_erathos]

I want to dynamically switch a column in a table and display the sum (or any aggregation) of values for the column which is selected.

I've looked at several solutions that don't work for my case, such as this article: Change Dimension Dynamically using Parameter in PowerBI, that requires a "Direct Query" connection, which I cannot use due to size of the data and more importantly because my connection (ODBC) does not support it. This post: Regarding Dynamic Y axis goes over a solution which involves unpivoting the columns, which is not an option for me again due to the size of the data and how the rest of the project is already set up. Guy in a Cube: 3+ approaches for Dynamic Axis in Power BI also covers some ideas, the bookmark being one that could almost work if it was possible to use a slicer to change bookmarks (otherwise there's no practical way of displaying all the buttons for the options required).

I've simplified my model to still contain the problem:

 

 

What I want to be able to do (be it with a Parameter or Disconnected Table) is to switch between the fields in each dimension using a slicer. I manually built a couple tables that simulate this funcionality:

    

Is there a way to do this? Here is also a link to the .pbix file which I used to build this example.

Thank you for the help!

[v-kkf-msft]

Hi @victor_erathos ,

Please create the new table and inactive relationship.

Slicer = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Dim1, "Field", "type", "Value", Dim1[type] ),
        SELECTCOLUMNS ( Dim2, "Field", "city", "Value", Dim2[city] ),
        SELECTCOLUMNS ( Dim1, "Field", "name", "Value", Dim1[name] )
    )
)

Then create the measure to calaculate total value.

Measure = 
VAR city_sumValue =
    CALCULATE ( [SumValue], USERELATIONSHIP ( Slicer[Value], Dim2[city] ) )
VAR type_sumValue =
    CALCULATE ( [SumValue], USERELATIONSHIP ( Slicer[Value], Dim1[type] ) )
VAR name_sumValue =
    CALCULATE ( [SumValue], USERELATIONSHIP ( Slicer[Value], Dim1[name] ) )
RETURN
    SWITCH (
        SELECTEDVALUE ( Slicer[Field] ),
        "city", city_sumValue,
        "type", type_sumValue,
        "name", name_sumValue
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[victor_erathos · Accepted solution]

Thank you, @v-kkf-msft !

I ran into a few more issues, since my original data had NULL values in the dimension tables, as well. Anyway, got through those and ended up bringing that aditional table directly with SQL.

However, the solution unfortunately introduces some other issues, given that we would be using an inactive relationship on this new table with distinct values. This means that, if I want to build an interactive report with the visuals filtering each other, I can't just click on a value of this table we are building to filter the other visuals, since, e.g. Dim1[type] is not related to the values in the table directly. This is an integral part of my problem; I am actually using scatter plots and not a table (which, for the purpose of the problem, it's all the same, that's why I kept it simple) and the user needs to be able to filter the report based on the data points in the visual.

Your solution does solve the problem I layed out on the post, so if you do not have any other suggestions with regard to the issues I mentioned, I'll close the thread.

[tackytechtom]

Hi @victor_erathos ,

I think your issue could be solved with calculation groups.

Here is a blog post where they discuss similar ideas:

Top 10 uses for Calculation Groups — Apex Insights: Power BI tips & tricks 

Hope this gets you closer to a solution! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/