DAX Help - Linking individual lines of sales to a Transaction number

[Domp28]

Morning,

I've been tasked with answering the questions "How many transactions contain only Card?" & "How many transactions contain Card & Party?" etc.. Due to the sheer amount of rows, I've been reccomended to use BI for this analysis, a tool I'm new to. I've attached a sample snippet of the available data and relationships below:

There are often numerous "Category Splits" within a transaction, so I'm needing to write a new column that combines all the Category splits to their transaction number, which can then be used to define how many are Card only etc.. Any advice on a measure that may be able to achieve this (With the data available) would be much appreciated?

Thanks in advance!!

[johnt75]

First create a table to use in a slicer, like

Categories = DISTINCT( 'Line Level Transactions'[Category Split])

but do not link this to your transactions table, leave it disconnected. Then you can create a measure like

Num matching transactions =
VAR NumSelectedCategories =
    COUNTROWS ( VALUES ( 'Categories'[Category Split] ) )
VAR Transactions =
    ADDCOLUMNS (
        VALUES ( 'Line Level Transactions'[Transaction] ),
        "@all items", CALCULATE ( COUNTROWS ( 'Line Level Transactions' ) ),
        "@selected items",
            CALCULATE (
                COUNTROWS ( 'Line Level Transactions' ),
                TREATAS (
                    VALUES ( 'Categories'[Category Split] ),
                    'Line Level Transactions'[Category Split]
                )
            )
    )
VAR Result =
    COUNTROWS (
        FILTER (
            Transactions,
            [@all items] = NumSelectedCategories
                && [@selected items] = NumSelectedCategories
        )
    )
RETURN
    Result

which will return the number of transactions which consist only of the selected combination of categories.

[Domp28]

Thank you for this John, really appreciate the support! The solution looks to be working correctly for situations in which there is 1 item in a transaction e.g Selecting "Card" in the slicer brings back all transactions that have one card in the basket.

However, if there are multiple items in one transaction, these look to be miss-assigned to the incorrect Category Splits, please see example below:

Don't suppose you have any further ideas to ammed this please?

Thank you!

[johnt75 · Accepted solution]

I think you could tweak it to

Num matching transactions =
VAR NumSelectedCategories =
    COUNTROWS ( VALUES ( 'Categories'[Category Split] ) )
VAR Transactions =
    ADDCOLUMNS (
        VALUES ( 'Line Level Transactions'[Transaction] ),
        "@all items",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Line Level Transactions'[Category split] ) )
            ),
        "@selected items",
            CALCULATE (
                COUNTROWS ( DISTINCT ( 'Line Level Transactions'[category split] ) ),
                TREATAS (
                    VALUES ( 'Categories'[Category Split] ),
                    'Line Level Transactions'[Category Split]
                )
            )
    )
VAR Result =
    COUNTROWS (
        FILTER (
            Transactions,
            [@all items] = NumSelectedCategories
                && [@selected items] = NumSelectedCategories
        )
    )
RETURN
    Result

[Domp28]

Brilliant that's worked now, thank you!!