Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

POWER BI visualizations - multiple columns with different status in same report

  Hi,   I have one report that contain multiple columns (Status 1 to status 7) with different status (Okay, Not Okay, Completed and Not completed), each columns has different count with different ...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hello @Saxon10 ,

    Is the second screenshot the result of your desire?

    If yes, we suggest that you create a new status table and seven measures. See the following steps.

    1. Create a table with Enter Data.

    Po1.jpg

    2. Create seven measures and place them in a table visual.

    STATUS1 Measure = 
CALCULATE(DISTINCTCOUNT(DATA[ITEM]),FILTER(DATA,DATA[STATUS1]=MAX('Table'[State])))+0

    Po2.jpg

    If you don't meet your requirements, could you show the exact expected result based on the table you shared?

    Best regards

    Community support team _ zhenbw

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

    BTW, pbix as an attachment.

AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

I would try using the first table you posted - then in Power Query Editor select Status 1 through Status 7, click Home tab > Unpivot Columns. You will now have three columns: ID, Attribute and Value (you can rename these as you wish). 

 

Close and Apply the changes. 

 

Create a new measure: 

Distinct Count Items = DISTINCTCOUNT(Table[ID])

 

Then create a visual with [Distrinct Count Items] in Values, [Attribute] in Axis and [Value] in Legend.

 

Not sure if that will give you what you're looking for exactly, but you should be able to play around with the combinations from here.

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

Hi. Thanks for your reply and suggestions. Those 7 status came from DAX function measure so unable to use the power query editor options.