Forum Discussion
Dynamic text column switch for aggregation
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.
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.
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.