Crosstab visualization with dynamically changing columns

[dfarkas]

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:

new_table = SUMMARIZE(table,[COLUMNS THAT ARE SPECIFIED BY THE USER], "Count", DISTINCTCOUNT(table[Id]))

Two requirements: 

- this type of matrix visualization

- always with the columns the user specified

I could easily solve this with python/dash, but I couldn't find a good solution with Power BI in the last two workdays...

[Anonymous]

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 Qin

[dfarkas]

Sorry, it didn't help

[Anonymous]

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.

[Anonymous]

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.

[dfarkas]

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

[PaulDBrown]

You could do this be creating a custom matrix with a slicer for columns:

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1... 

[amitchandak]

@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/

[dfarkas]

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