Customer who Bought A, what else he bought ?

[Anonymous]

Hi,

I have data similar to below:

ID	Acquisition Qtr	Purchase Qtr	Product	Acq Type
1	19Q1	19Q1	A	At Acq
1	19Q1	18Q1	B	Pre Acq
1	19Q1	20Q1	C	Post Acq
2	19Q2	19Q2	C	At Acq
2	19Q2	18Q2	B	Pre Acq
2	19Q2	20Q2	A	Post Acq

I want to answer the following question :

1. What products were purchased at the time of Acquisition ? Like above, it is A & C.

2. What products were purchased prior and post acquisition ? For ex : If we select A in step 1, then I should be able to get that B product was purchased pre Acq and C was purchased post Acq.

I am new to power BI and DAX, it would be great help if you could provide me with appropriate code / measure or sample pbix file to perform the same.

Thanks,

Anshul Bhandari

[Anonymous]

// If you want to show the products in
// a table visual, then you have to:
// 1. Create a model table with all the products
//    and name it something like "Post-Acq Prods".
// 2. Create a model table with all the products
//    and name it something like "Pre-Acq Prods".
// 3. Keep them DISCONNECTED.
// 4. Create 2 measures (below).
// 5. Put the two tables' Product columns into
//    2 different table visuals (pre- and post-acq products).
// 6. Set visual filters on the tables using
//    the Filter pane and the 2 measures below
//    (filter by [measure] = 1).
// If you now create a slicer on the products
// from the T table (the one you showed), the two
// table visuals mentioned above will display
// the associated pre- and post-acq products.

[Is Pre-Acq Product?] =
var __acqTypeOfInterest = "pre acq"
// Let's gather all the Pre-Acq
// products that correspond to those
// "At Acq" for the same ID's and
// Acquisition Qtr. Ignore the products
// that are not "At Acq".
var __preAcqProds = 
	CALCULATETABLE(
		VALUES( T[Product] ),
		filter(
			SUMMARIZE(
				T,
				T[ID],
				T[Acquisition Qtr],
				T[Acq Type]
			),
			T[Acq Type] = "at acq"
		),
		T[Acq Type] = __acqTypeOfInterest,
		ALL( T )
	)
var __visiblePreAcqProd =
	SELECTEDVALUE( 'Pre-Acq Product'[Product] )
return
	1 * ( __visiblePreAcqProd in __preAcqProds )
	

[Is Post-Acq Product?] =
var __acqTypeOfInterest = "post acq"
// Let's gather all the Pre-Acq
// products that correspond to those
// "At Acq" for the same ID's and
// Acquisition Qtr. Ignore the products
// that are not "At Acq".
var __postAcqProds = 
	CALCULATETABLE(
		VALUES( T[Product] ),
		filter(
			SUMMARIZE(
				T,
				T[ID],
				T[Acquisition Qtr],
				T[Acq Type]
			),
			T[Acq Type] = "at acq"
		),
		T[Acq Type] = __acqTypeOfInterest,
		ALL( T )
	)
var __visiblePostAcqProd =
	SELECTEDVALUE( 'Post-Acq Product'[Product] )
return
	1 * ( __visiblePostAcqProd in __postAcqProds )

Please note that the above measures are helper measures that should only be used to filter (=1) the rows of a table visual in which products from one of the two tables I mention should be stored.

[amitchandak]

@Anonymous , Create measures like this and use with product or customer and product

At Acq= calculate(sum(Table), filter(Table,Table[Acq Type] = " At Acq"))

Pre Acq= calculate(sum(Table), filter(Table,Table[Acq Type] = "Pre Acq"))

Post Acq= calculate(sum(Table), filter(Table,Table[Acq Type] = "Post Acq"))

Or you can use a slicer for Acq Type

[Anonymous]

Thanks, But your solution is not giving me appropriate result. I want to create a view where if I select Product A at ACQ, then all the accounts who have purchased Product A at ACQ, and what other products were purchased in Pre Acq and Post Acq by those accounts who were Acquired by Product A at ACQ.

[Greg_Deckler]

@Anonymous - You could create what is called a slicer visualization on [Acq Type]. When you select things from this slicer your other visuals will filter automatically.