Get a custom table in DAX to work with filters from the source table

[markandrewolive]

What I want to do:

I conducted a survey, and I want to create a chart that looks like this:

... but I want to be able to use all of the other data available to me as filters.

My data:

My data for this question looks like this:

The only way I could think of to make that work in a like pretty likert scale chart was to use DAX to create a table that looks like this:

Which was built using a fairly painstaking DAX formula that creates rows like this:

("Cost",
            "Yes",
            1,
            CALCULATE(COUNT('Complete Results'[Cost]),
                'Complete Results'[Cost] = "Yes"))

The Problem:

As far as I'm concerned, since my custom DAX table uses the data from my 'Complete Results' table, it should automatically respond to filters from that table without giving me any problems.

But it doesn't. It complete ignores them.

What do I have to do to get my custom DAX table to work with the filters in the source table?

[v-jingzhang]

Hi @markandrewolive 

Your DAX table is a calculated table, right? A calculated table is not able to be filtered by filters/slicers in the report. The data in it is calculated when you create it and it only changes when you refresh the model. You need to use measures in the visual instead because measures can be influenced by user interaction with filters/slicers in the report. 

Can you give a simple example that which column is from the source table and is used to filter the bar chart?

Best Regards,
Community Support Team _ Jing

[amitchandak]

@markandrewolive , I think you should Unpivot the table in power query.

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

[markandrewolive]

I'm not sure I understand. Why would that help?