Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a report from a table with many binary values

Hi, 

I want to create a barchart from an order table that has 30 products stored as binary values. Example. Product A = TRUE.

 

I want to create a chart where I can see in one go the frequency of these products by yearquarter (actually values are not completely binary as they are TRUE/FALSE/BLANK). And I want to have a table visual in the same report so that when I click on the visual frequency I will see the records in the table visual. Example Product A, when I select the barchart frequency of A, I want to see Order 1 in the table visual.

 

For this I started to create measures in the Order table, that count the number of rows:

Product A = COUNTAX(FILTER(Orders; Orders[Product A]=TRUE());[Product A]), and I put the measures in the barchart. This works but this does not filter through to the table visual of course. 
 
Is there a (more efficient) way to make this work. 

 

OrderProduct AProduct BProduct CProduct DOrder date
1TRUEBLANKFALSETRUEddmmyy
2FALSETRUEBLANKTRUEddmmyy
3FALSEFALSETRUETRUEddmmyy

 

Kind regards,

 

Bram

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    After unpivoting your table, you could use matrix visual to show your data. Put product names in the "Column" option and put order numbers in the "Row" option. 

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg Hi thanks for the input. Considered yes, but I will try that. I was thinking my report table visual would then also look like an unpivoted table, something that I don't want. 

       

      Order 1 - Product A

      Order 1 - Product D 

      ...

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous 

        Please consider unpivot.

        https://radacad.com/pivot-and-unpivot-with-power-bi

         

        You can use summarize, select column and union . but not suggested

        Union(
selectcolumns(table,"Order",table[Order],"Product","Product A", "Date",Table[Order date],"Value",table[Product A]),
selectcolumns(table,"Order",table[Order],"Product","Product B", "Date",Table[Order date],"Value",table[Product B]),
selectcolumns(table,"Order",table[Order],"Product","Product C", "Date",Table[Order date],"Value",table[Product C]),
selectcolumns(table,"Order",table[Order],"Product","Product D", "Date",Table[Order date],"Value",table[Product D])
)

         

        Appreciate your Kudos.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    After unpivoting your table, you could use matrix visual to show your data. Put product names in the "Column" option and put order numbers in the "Row" option.