Count values for multiple columns

[Anonymous]

I think the title is not clear at all. I have to explain my issue.

I have a table with few columns (9 in the screenshot below)

I have to create a bar chart based on thise data. The problem is based on the logic I want to apply.

If there is a value in qa1_1_2, I want to count towards qa1_1_1 as well. If qa1_1_9 has a value, I want to count towards qa_1_1 to qa_1_9. For example, if a patient takes two doses of a tablet (qa_1_2), obviously it takes also one dose (qa_1_1). So, I want to count both columns in the graph.

I hope it is clear what I'm trying to achieve.

[Icey · Accepted solution]

Hi @Anonymous ,

You can try to use UnPivot to transform your data like so:

pass	Attribute	Value
xxx	qa1_1_1	1
xxx	qa1_1_2	0
xxx	qa1_1_3	0
xxx	qa1_1_4	0
...	...	...

Then, create a measure to count column "Value" for each "pass".

Reference: Pivot and Unpivot with Power BI - RADACAD

If this is not what you want, please give me more details.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.