How to Use the Names of a series of Columns as Stacked Bar Chart Y-axis Values/Labels

[LijunChen]

Dear Forum members, 

I have a question about how to use a series of column names with binary values (1/0) in a stacked barchart with the column names as stacked bar Y value labels. 

Specifically, I imported a table into Power BI about the health conditions of some children, with each row/record as a single child.  The table has a series of columns indicating different health conditions a child may have. See screenshot below. These include intellectural_disability, autism, visual impairment, etc. The values can be 1 (have the condition), 0 (no condition), null (not available).

I would like to show the number of children for any of the conditions in a matrix table, with the row headers being the series of health conditions and values being the number of children for that condition. In the columns I would like to put child gender, race or age group to breakdown the data further. 

AS shown in screenshot below, I can compute a series of measures for the number of childrne with each condition. I can use these measures in a matrix table as the row headers (see 1st matrix table below in screenshot). The gender is used as column headers.

However, I cannot convert this to a stacked horizonal bar chart with the health condition measures' names as the Y label/value. In the bar chart shown in above screenhot, I would like to use the series of health condition measures as the Y labels, and gender / race etc. as legends of the bar chart. But the bar chart visual do not seem to be able to do that..

I created a parameter of the measues, but the parameter cannot be used as a Y axis measure, either.

I eventually thought out a roundabout way to do this, but it is very cumbersom / time consuming. I replaced the "1" value of each column of the health series to be their column names.  I then merge these text values of health conditions as a single column of all the condtions selected for each person. I then created a separate table with a list of the names of columns, and their display name. I then create a measure that counts the number of rows in the conbined health condition column that contain any of the column names shown in the separate table (serving a kind of lookup value) .  The measure looks like this:

Count_healtcond =

VAR _search_world=

    SELECTEDVALUE(List_HealthCondtion[searchword])

VAR _result =

    CALCULATE (

        COUNTROWS ('childtable_2023'),

        CONTAINSSTRING('childtable_2023'[health_merge], _search_world)

    )

RETURN

    _result

Then I use the separate list column display name as the bar Y label, and measures as the value . See screenshot below: 

I wonder if there is an easy /straightforward way to do this using the series of health conditons' names in a parameter. Any of you help is appreciated. 

Lijun 

[FarhanAhmed · Accepted solution]

Try Unpivot your all health condition columns in Power Query. This will simplify the model.

Then use this Health condition column in your Y-Axis and gender in Legend

[FarhanAhmed · Accepted solution]

Try Unpivot your all health condition columns in Power Query. This will simplify the model.

Then use this Health condition column in your Y-Axis and gender in Legend

[LijunChen · Accepted solution]

@FarhanAhmed Thanks, Farhan. 

Your approach is easy to perform and much better than what I did. Though I will need to create a separate table with the selected variables.