visual showing only merchants with both online and in-store transactions

[Johnn]

Hi,

I have a fact table on transaction history linked to a merchant table. I have a column in the transaction table that signifies if the transactions were done in-store or online. I'm having a little difficulty trying to figure out how to create a visual that only shows merchants which have seen both online and in-store transactions. Thanks for any help!

[v-yulgu-msft]

Hi @Johnn,

From current information, it's hard to imagine how is your table structure like and what is your desired result. Please provide sample data, and tell us how to link the transaction table and merchant table. What is the common column between these two tables? And what do you mean 'only shows merchants which have seen both online and in-store transactions'?

Regards,
Yuliana Gu

[Johnn]

Hi,

The tables are structured as below. There is a one to many relationship between the Merchant Table and Transaction Table on Merchant ID. My desired result is that I want to create a visual that only shows Merchant A or the merchants that saw both online and instore transactions.

One way I tried to do it is to create two new columns in my merchant table:

Online Transactions = Countrows(CALCULATETABLE(relatedtable('Transaction Table'), 'Transaction Table'([online/instore]="online"))

Instore Transactions = Countrows(CALCULATETABLE(relatedtable('Transaction Table'), 'Transaction Table'[online/instore]="instore"))

And then in my visuals add a visual level filter where [Online Transactions] is not blank and [Instore Transactions] is not blank. But not sure if this is the most efficient way to do it and also it could be nice to have it be time dependent so if for example a merchant first started with both online and instore transactions and then just switched to only instore transactions I would be able to filter them out if my visual was only over that period of time where they only had instore transactions.

[v-yulgu-msft · Accepted solution]

Hi @Johnn,

You could consider an other solution. Please create below measures. Add measure [instore & online] into visual level filter, set its value to 2.

Max date =
CALCULATE (
    MAX ( 'Transaction'[Date] ),
    ALLEXCEPT ( 'Transaction', 'Transaction'[MerchantID] )
)

instore & online =
CALCULATE (
    DISTINCTCOUNT ( 'Transaction'[online/instore] ),
    FILTER ( 'Transaction', 'Transaction'[Date] = [Max date] )
)

Best regards,
Yuliana Gu