Create a report from a table with many binary values

[Anonymous]

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. 

Order	Product A	Product B	Product C	Product D	Order date
1	TRUE	BLANK	FALSE	TRUE	ddmmyy
2	FALSE	TRUE	BLANK	TRUE	ddmmyy
3	FALSE	FALSE	TRUE	TRUE	ddmmyy

Kind regards,

Bram

[v-eachen-msft · Accepted solution]

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. 

[Greg_Deckler]

Have you considered unpivoting your Product columns?

[Anonymous]

@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]

@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.