Forum Discussion
Crosstab visualization with dynamically changing columns
Hello,
I have the following table:
Id Column1 Column2 Column3 .... Column N
0 cat male house
1 dog male car
2 cat female car
...
N
From this, I'd like to show the output for the user that can be visualized as a groupby table:
8 Replies
dfarkas , You need to unpivot the columns and they will become row values, You can use them in the slicer and matrix column.
refer if needed
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/Thanks, amitchandak.
This was the first solution I tried and unfortunately this doesn't suit my requirements. The main problem is that this is an additive solution and does not do any cross-tabulation.
This is the output from this solution (N = 10):
Column 1 Count
cat 3
dog 7
Column 2
male 5
female 5
Total 20
Instead, I'm looking for an output like this:
Column 1 Column 2 Count
cat male 2
cat female 4
dog male 1
dog female 3
Total 10
Hi dfarkas ,
If you just want the expected output , you could drag the Id field to Values and choose "Count" summarized type like this:
Or you could use the following formula to create a summarized table:
NewTable = SUMMARIZE('Table','Table'[Column1],'Table'[Column2],"Count",COUNTROWS('Table'))Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Eyelyn,
Thank you for your support. This is what I want and I could do this.
What I'm struggling with is to include a slicer (or slicers) which let's the user choose which columns to include.
E.g only Column 1, or Column 2 and 3, or all of them. (In my actual data, I have 38 columns and 1 to 3 selections should be possible to the user).
Best,
Dávid
You could do this be creating a custom matrix with a slicer for columns:
Hi dfarkas ,
When you change the value of slicers , the value generated is dynamic.
You could not pass this to a Calculated Table or a Calculated Column.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi dfarkas ,
Could you tell me if my post helps you ? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.
Best Regards,
Eyelyn QinSorry, it didn't help
