topic Re: Customer who Bought A, what else he bought ? in DAX Commands and Tips

Customer who Bought A, what else he bought ?

Anonymous — Wed, 09 Sep 2020 19:53:38 GMT

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

Re: Customer who Bought A, what else he bought ?

Greg_Deckler — Wed, 09 Sep 2020 20:27:28 GMT

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.

Re: Customer who Bought A, what else he bought ?

amitchandak — Thu, 10 Sep 2020 02:49:19 GMT

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

Re: Customer who Bought A, what else he bought ?

Anonymous — Thu, 10 Sep 2020 06:31:10 GMT

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.

Re: Customer who Bought A, what else he bought ?

Anonymous — Thu, 10 Sep 2020 11:45:22 GMT

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