Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
// 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.
@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
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.
@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.
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |