topic Re: DAX COMMAND in DAX Commands and Tips

DAX COMMAND

Gazanfar — Wed, 08 Jun 2022 14:08:51 GMT

I have data in excel as shown below.

C1_1 C1_2 C1_3 C1_4 C1_5

Affordable	Affordable	Do not know	Do not know	Affordable
Affordable	Affordable	Do not know	Do not know	Do not know
Affordable	Affordable	Do not know	Affordable	Affordable
Affordable	Affordable	Do not know	Do not know	Affordable
Not affordable	Not affordable	Do not know	Do not know	Not affordable
Affordable	Do not know	Affordable	Affordable	Affordable
Affordable	Do not know	Do not know	Do not know	Affordable
Affordable	Do not know	Not affordable	Do not know	Do not know
Affordable	Affordable	Do not know	Do not know	Do not know
Affordable	Do not know	Do not know	Do not know	Do not know
Affordable	Affordable	Do not know	Do not know	Do not know
Affordable	Affordable	Do not know	Do not know	Do not know

And I want to get visualization in Power BI as shown below. I do not know which measures I have to use to get this kind of table in Power BI. If anyone knows please help. Thanks in advance!

Re: DAX COMMAND

Syk — Wed, 08 Jun 2022 15:08:21 GMT

Once you load your data, click "transform data" to bring up your power query window.
Once that window opens, select all of your columns and then under the transform tab, click unpivot columns
This should give you a table with 2 columns - Attribute and Value (see screenshot using your example data)

Close and apply these changes.
Add a matrix visualization to your canvas, add the Attribute field to Rows and Value to columns
Then add your Attribute field to the values. (It should change to "First Attribute" because it is a text field) click the arrow next to it and select "Count" instead of "First".
Then select the same arrow and under "Show value as" select "Percent of row total" for your use case. Then remove the totals from the matrix and you should have what you need!

Re: DAX COMMAND

Gazanfar — Thu, 09 Jun 2022 07:38:20 GMT

Thank you Syk, you explained everything in detail and your advice helped me too much!

Re: DAX COMMAND

Syk — Thu, 09 Jun 2022 12:25:01 GMT

Great to hear! Consider marking that reply as the solution so others can easily find it if they have a similar issue!