Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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
Solved! Go to 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
Proud to be a Super User!
@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.
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
Proud to be a Super User!
@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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
89 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
118 | |
101 | |
76 | |
68 |