Matrix with Dynamic Row/Column Headers

[BenjyPowerBI]

Hi! 

Let's say I have a table with the columns: ID, Attribute 1, Attribute 2, Attribute 3...  where each of the attribute fields can be either A, B or C in value. 

I'm trying to create a matrix visual whose row headers are directly linked to a slicer which lets you choose the attribute (1, 2 or 3) and whose column headers are directly linked to a second slicer which lets you choose the attribute. The matrix values when they show how many IDs are within each combination of attribute values. 

Is there a way to achieve this using DAX alone and without producing a great big unpivot table?

Thanks,

Ben

[PaulDBrown]

@BenjyPowerBI 

A very good question for which I have no answer! (I would say go for it...)

Ps: and report your findings if you do so!

[amitchandak]

@BenjyPowerBI , Unpivot this data. That will give Attribute and value , Use Attribute and value both row and column and do count of ID

https://radacad.com/pivot-and-unpivot-with-power-bi

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[BenjyPowerBI]

Hey Amit,

The inclusion of an unpivot table makes the .pbix file size way too big to work with (and publish) so I need a workaround using DAX if possible 😕

[PaulDBrown]

@BenjyPowerBI 

Work with a filtered dataset to reduce the size and reconnect to the full dataset once published

[BenjyPowerBI]

Hi Paul I'm not sure what you mean by this. Please could you elaborate? 🙂 And how can I reconnect to the full dataset after it's been published? 

Thanks in advance!

[PaulDBrown]

@BenjyPowerBI 

Here are a couple of links which will help (I hope!)

Edit parameters settings in Power BI service 

Change the source of PowerBI datasets dynamically using Power Query parameters 

So basically you create a parameter to filter down the dataset to a manegeable level, and once published, you edit the parameter in the service.

OR, work with a filtered sample and then alter the source from within the service to pull the whole dataset (as per link number 2)

[BenjyPowerBI]

At the risk of sounding stupid, I'm currently limited to (I think) a 1024MB publishing limit so whenever I try to publish a file with a size greater than that I get blocked. Does this mean that by parameterising it I can bypass this limit?

[v-shex-msft]

Hi @BenjyPowerBI,

In fact, query parameters not able to affect this limit.

It should be a trick to designing a report with fully parameterized connection string reports to skip the publish file size checking.

After these steps, you can change datasource on the power bi service side, but it not means the 1GB dataset limit has been resolved. If you change on the service side, this size limit issue still exists if your dataset expanded. (dataset size limit has been increased on premium license)

What is Power BI Premium?#size-considerations 

Regards,

Xiaoxin Sheng