How to create stack bar chart from 4 different tables in Power BI

1
votes

I have 4 different tables that are not joined to each other and all have same column names: 'Status' and 'Count'. I want to create stuck bar chart that would display count for each Status. As an example I'm using first two tables: Overall_Results and PredictedBoundResults

enter image description here

So I put column Status on Axis, Count on Value and it works fine. But if I put second Count in value then it gives me total number, instead of breaking out by Status.

enter image description here

So my question: if I bring ID column to all those 4 tables and join them together, will that give me desirable result?

.ipbx can be accessed here: https://www.dropbox.com/s/ursrwhyz9xqtd3c/PredictionsUnderwrProducer.pbix?dl=0

1
powerbidaxpowerbi-embedded

1 Answers

1
votes

Yes, this is possible. The easiest way, is to create a conformed dimension, just for Status. In this table, you collect all possible (distinct) values of Status. You can do this with UNION and VALUES/DISTINCT.

After that, connect the status column (from the new table) to all four tables. Then you can report on Status, like you want.

Edit, like this:

Calculated table:

DimStatus = 
DISTINCT (
FILTER (
    UNION (
        UNION (
            SELECTCOLUMNS ( PredictedBoundResults; "Status"; PredictedBoundResults[Status] );
            SELECTCOLUMNS (
                PredictedNotBoundResults;
                "Status"; PredictedNotBoundResults[Status]
            )
        );
        UNION (
            SELECTCOLUMNS ( Overall_Results; "Status"; Overall_Results[Status] );
            SELECTCOLUMNS ( Not_Predicted; "Status"; Not_Predicted[Status] )
        )
    );
    NOT ISBLANK ( [Status] )
)
)
  • Connect to all four statusses

Result:

Result