topic Re: Summarize Data for Venn Diagram in DAX Commands and Tips

Summarize Data for Venn Diagram

Anonymous — Mon, 15 Nov 2021 18:52:36 GMT

I'd like to be able to use the Venn Diagram by MAQ software. However, it requires the data to be summarized into a pivot, which negates the use of a slicer for viewing data subsets. I'm fairly new to PowerBI, but it seems like there might be a dax formula to allow me to use my current slicer but creates a subset based on something like the following?

select 1[ColumnA],0[ColumnB],0[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=0 and colC=0 and colD=0 union
select 0[ColumnA],1[ColumnB],0[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=1 and colC=0 and colD=0 union
select 0[ColumnA],0[ColumnB],1[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=0 and colC=1 and colD=0 union
select 0[ColumnA],0[ColumnB],0[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=0 and colC=0 and colD=1 union
select 1[ColumnA],1[ColumnB],0[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=1 and colC=0 and colD=0 union
select 0[ColumnA],1[ColumnB],1[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=1 and colC=1 and colD=0 union
select 0[ColumnA],0[ColumnB],1[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=0 and colC=1 and colD=1 union
select 1[ColumnA],0[ColumnB],0[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=0 and colC=0 and colD=1 union
select 1[ColumnA],1[ColumnB],1[ColumnC],0[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=1 and colC=1 and colD=0 union
select 0[ColumnA],1[ColumnB],1[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=0 and colB=1 and colC=1 and colD=1 union
select 1[ColumnA],0[ColumnB],1[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=0 and colC=1 and colD=1 union
select 1[ColumnA],1[ColumnB],0[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=1 and colC=0 and colD=1 union
select 1[ColumnA],1[ColumnB],1[ColumnC],1[ColumnD],count(myKey)[Totals] from #u2 where colA=1 and colB=1 and colC=1 and colD=1

Re: Summarize Data for Venn Diagram

amitchandak — Tue, 16 Nov 2021 03:29:42 GMT

Anonymous , have you checked pivot/ unpivot option in Power Query?

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

Pivot Data(Power Query) :https://www.youtube.com/watch?v=oKByyI09Bno&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=12

Unpivot Data(Power Query) https://www.youtube.com/watch?v=2HjkBtxSM0g&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=11

Re: Summarize Data for Venn Diagram

Anonymous — Tue, 16 Nov 2021 16:06:30 GMT

I'm not seeing how the pivot would work? The Venn by MAQ requires the data to be in a specific format (see above) and does not do any aggregate summarization. So, I couldn't have the link on my key in order to have a slicer applicable or be able to do a pivot on my category in order to get totals. Does that make sense?

Re: Summarize Data for Venn Diagram

v-jingzhang — Fri, 19 Nov 2021 09:48:38 GMT

Hi Anonymous

I feel that it's difficult to create a measure to get dynamic aggregate summarization for measure field in the Venn Diagram by MAQ software visual. Maybe you can consider creating a custom Venn Diagram with multiple pie charts and card visuals. It seems more flexiable. See Power BI - Creating a Custom Venn Diagram - YouTube

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.