Value of bundles

[clausm73]

I have a problem trying to calculate the value of bundles - and only bundles.

Transactions

Date	SKU	Amount	CustomerID
01-01-2020	9915	4000	1
01-01-2020	1050	0	1
01-02-2020	9900	96000	1
01-02-2020	1141	1500	1
01-02-2020	1050	0	1
01-02-2020	1000	0	1

Bundles

BundleID	ItemSKU	BundleName
1	9915	Bundle 1
1	1050	Bundle 1
2	9900	Bundle 2
3	1141	Bundle 3

My expected output should look like:

Date	SKU	Amount	CustomerID	BundleID	ItemSKU	BundleName	Bundle Item Count	Tranaction Item Count
01-01-2020	1050	0	1	1	1050	Bundle 1	2	2
01-02-2020	1050	0	1	1	1050	Bundle 1	2	1
01-01-2020	9915	4000	1	1	9915	Bundle 1	2	2
01-02-2020	9900	96000	1	2	9900	Bundle 2	1	1
01-02-2020	1141	1500	1	3	1141	Bundle 3	1	1

In the end I will filter only the rows where "Bundle Item Count" and "Transaction Item Count" matches.

SQL Query to get result:

WITH transactions AS (
SELECT *
FROM (
VALUES
('2020-01-01', 9915, 4000, 1)
,('2020-01-01', 1050, 0, 1)
,('2020-02-01', 9900, 96000, 1)
,('2020-02-01', 1141, 1500, 1)
,('2020-02-01', 1050, 0, 1)
,('2020-02-01', 1000, 0, 1)
) AS t([Date], [SKU], [Amount], [CustomerID] )
), bundles as (
SELECT *
FROM (
VALUES
(1, 9915, 'Bundle 1')
,(1, 1050, 'Bundle 1')
,(2, 9900, 'Bundle 2')
,(3, 1141, 'Bundle 3')
) AS bundles([BundleID], [ItemSKU], [BundleName])
), result AS (
SELECT
*
,[Bundle Item Count] = (SELECT count(*) FROM bundles WHERE [BundleID] = b.[BundleID])
FROM transactions c, bundles b
WHERE 1=1
AND c.[SKU] = b.[ItemSKU]
)
SELECT *
,[Tranaction Item Count] = (SELECT count(*) FROM result WHERE [BundleID] = m.[BundleID] and [Date] = m.[Date])
FROM result m

So far I have been able to get the following DAX but its not quite sufficient:

EVALUATE
ADDCOLUMNS (
ADDCOLUMNS (
GENERATE (
transaction;
FILTER ( bundles; transactions[SKU] = bundles[ItemSKU] )
);
"Bundle Item Count"; CALCULATE (
COUNTA ( bundles[ItemSKU] );
ALLEXCEPT ( bundles; bundles[BundleID] )
)
);
"Transaction Item Count"; CALCULATE (
COUNTA ( transactions[SKU] );
ALLEXCEPT ( transactions; transactions[Date] )
)
)

Any help would be appreciated,
Claus

[camargos88]

Hi @clausm73 ,

I've created this file: Download PBIX 

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[clausm73]

Thanks for the effort - but my issue is not solved.

This row is displayed as Bundle 1 - and I want it NOT to be - because the Transaction Item Count <> Bundle Item Count

Date	SKU	Amount	CustomerID	BundleID	ItemSKU	BundleName	Bundle Item Count	Tranaction Item Count
01-02-2020	1050	0	1	1	1050	Bundle 1	2	1

That why I need to create the Transaction Item Count in the first place - which I cannot solve.

[clausm73]

I have managed to do a "workaround".

Created a column [BundlePath] in both tables using concatenation of column [ItemSKU] for each bundle - or potential bundle.
This column can then be used as a key in a relationship - or the [ItemSKU] values can be counted using PATHLENGTH on the concatenated value.

These counts - one for in 'bundles'[BundlePath] and one for 'transactions'[BundlePath] can then be compared in a new column [IsBundle].

Calculated column 'bundles'[BundlePath]

BundlePath =
CONCATENATEX (
CALCULATETABLE ( 'bundles'; ALLEXCEPT ( 'bundles'; 'bundles'[BundleID] ) );
'bundles'[ItemSKU];
"|";
'bundles'[ItemSKU]
)

 Calculated columns 'transaction'[LookupBundleID], 'transactions'[BundlePath] and 'transactions'[IsBundle]:

LookupBundleID =
LOOKUPVALUE ( Bundles[BundleID]; Bundles[ItemSKU]; Transactions[ItemSKU] )

 BundlePath =
CONCATENATEX (
CALCULATETABLE (
Transactions;
/* This is the essential statement to make it work correctly */
ALLEXCEPT (
Transactions;
Transactions[CustomerID];
Transactions[Date];
Transactions[LookupBundleID]
)
);
Transactions[ItemSKU];
"|";
Transactions[ItemSKU]
)

 IsBundle =
VAR _LookupBundleID = Transactions[LookupBundleID]
VAR _BundleItemCount =
CALCULATE ( COUNTA ( 'Bundles'[ItemSKU] ); Bundles[BundleID] = _LookupBundleID )
VAR _TransactionItemCount =
PATHLENGTH ( Transactions[BundlePath] )
RETURN
_BundleItemCount = _TransactionItemCount

 This gives the following result - based on the pbix file from the suggested answer:

[camargos88 · Accepted solution]

Hi @clausm73 ,

I've changed the file with a new column to filter if the qty of itens in blundle is the same as the qty in transaction by date/bundle.

I hope it helps.

Ricardo

[clausm73]

Based on your solution file I came up with a way to build calculated columns with concatenated [ItemSKU] values to solve my problem - and then compare item counts using PATHLENGTH() on the concatenated values.

Download PBIX