How to use one dimension in several slicers at the same time

[IOdzelashvili]

Hello everyone, have a great and productive week!

Imagine I have three tables

• First one is fact_transactions with the columns: ID, TransactionDate, TransactionAmount, MerchantID, ClientID
• Second one is dim_merchants with the columns: MerchantID, MCC
• Third table is dim_mcc with the columns: MCC, Category

Relationships between these tables are:

• One to many ( dim_mcc[MCC] filters to dim_merchants[MCC] )
• One to many ( dim_merchants[MerchantID] filters to fact_transactions [MerchantID] )

My issues is, that I want to have possibility to choose more than one MCC via slicer and choose the amount for them.

What I mean, I want to have 4 (or 6, 8, etc.) slicers on the report, from which I can select MCC_1 and MCC_2 and also select a specific amount range for each MCC and the output I want to have a list of clients that meet my condition, For example I want a list of clients who have transactions with MCC_1 merchants between $100 and $1000 and also have transactions with MCC_2 merchants between $50 and $300.

Can someone help me to solve this problem? Thank you very much in advance!

[Anonymous · Accepted solution]

Hi @IOdzelashvili ,

From the description, it seems that multiple "dim_mcc" and "dim_transactionamount" tables are needed.

Then create a measure for the visual's filter:

MEASURE = 
VAR __mcc_1 =
    SELECTEDVALUE ( 'dim_mcc_1'[MCC] )
VAR __min_value_1 =
    MIN ( 'dim_transactionamount_1'[Value] )
VAR __max_value_1 =
    MAX ( 'dim_transactionamount_1'[Value] )
VAR __mcc_2 =
    SELECTEDVALUE ( 'dim_mcc_2'[MCC] )
VAR __min_value_2 =
    MIN ( 'dim_transactionamount_2'[Value] )
VAR __max_value_2 =
    MAX ( 'dim_transactionamount_2'[Value] )
VAR __cur_transaction_amount =
    SUM ( 'fact_transactions'[TransactionAmount] )
VAR __client_id =
    CALCULATETABLE (
        VALUES ( 'fact_transactions'[ClientID] ),
        ('dim_mcc'[MCC] = __mcc_1 && __cur_transaction_amount >= __min_value_1 && __cur_transaction_amount <= __max_value_1)
        || ('dim_mcc'[MCC] = __mcc_2 && __cur_transaction_amount >= __min_value_2 && __cur_transaction_amount <= __max_value_2)
    )
VAR __cur_client_id =
    SELECTEDVALUE ( 'fact_transactions'[ClientID] )
VAR __result =
    IF ( __cur_client_id IN __client_id, 1 )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[IOdzelashvili]

I have made several changes, but totally this helped me to solve my issue.

Thank you for your help!

[IOdzelashvili]

This is the answer from ChatGPT and if you look at this solution you will understand that things are missing here.

I also tried to use GPT, but I do not have successful result from it, so I came here 🙂 

[Swapnilmandloi]

Hello,
To achieve this functionality in Power BI where you can filter transactions based on multiple MCCs and specific amount ranges for each, you can follow these steps. This involves creating multiple slicers and DAX measures to filter and display the desired results. Here's a step-by-step guide:

Step-by-Step Guide

1. Load and Prepare Your Data
2. Create Measures for Filtering
3. Set Up Slicers for MCC and Amount Ranges
4. Display the Filtered Results

Detailed Steps

Step 1: Load and Prepare Your Data

1. Load Data into Power BI

   • Load your three tables: fact_transactions, dim_merchants, and dim_mcc into Power BI.

2. Establish Relationships

   • Ensure the relationships are set correctly:
     • dim_mcc[MCC] filters dim_merchants[MCC]
     • dim_merchants[MerchantID] filters fact_transactions[MerchantID]

Step 2: Create Measures for Filtering

1. Create Measures to Filter Transactions by MCC and Amount Range

   • Go to the Modeling tab and create new measures.

   • Measure for Transactions with MCC_1 within Range:

     DAX

     Transactions_MCC1 = CALCULATE( COUNTROWS(fact_transactions), FILTER( fact_transactions, fact_transactions[TransactionAmount] >= [SelectedAmountMin_MCC1] && fact_transactions[TransactionAmount] <= [SelectedAmountMax_MCC1] && RELATED(dim_merchants[MCC]) = [SelectedMCC1] ) )

   • Measure for Transactions with MCC_2 within Range:

     DAX

     Transactions_MCC2 = CALCULATE( COUNTROWS(fact_transactions), FILTER( fact_transactions, fact_transactions[TransactionAmount] >= [SelectedAmountMin_MCC2] && fact_transactions[TransactionAmount] <= [SelectedAmountMax_MCC2] && RELATED(dim_merchants[MCC]) = [SelectedMCC2] ) )

Step 3: Set Up Slicers for MCC and Amount Ranges

1. Create Parameters for MCC Selection

   • Go to the Modeling tab and create new parameters for MCC selections.

   • Parameter for Selected MCC 1:

     DAX

     SelectedMCC1 = SELECTEDVALUE(dim_mcc[MCC])

   • Parameter for Selected MCC 2:

     DAX

     SelectedMCC2 = SELECTEDVALUE(dim_mcc[MCC])

2. Create Parameters for Amount Range Selection

   • Parameter for Minimum Amount MCC 1:

     DAX

     SelectedAmountMin_MCC1 = SELECTEDVALUE('AmountRange'[MinAmount_MCC1])

   • Parameter for Maximum Amount MCC 1:

     DAX

     SelectedAmountMax_MCC1 = SELECTEDVALUE('AmountRange'[MaxAmount_MCC1])

   • Parameter for Minimum Amount MCC 2:

     DAX

     SelectedAmountMin_MCC2 = SELECTEDVALUE('AmountRange'[MinAmount_MCC2])

   • Parameter for Maximum Amount MCC 2:

     DAX

     SelectedAmountMax_MCC2 = SELECTEDVALUE('AmountRange'[MaxAmount_MCC2])

3. Create Slicers for MCC and Amount Ranges

   • Add slicers to your report for MCC selections and amount ranges.
   • Use the dim_mcc table for MCC slicers.
   • Use a new table for amount ranges with columns like MinAmount_MCC1, MaxAmount_MCC1, MinAmount_MCC2, MaxAmount_MCC2.

Step 4: Display the Filtered Results

1. Create a Table Visual to Display Results

   • Add a table visual to your report.
   • Add columns for ClientID and any other relevant information.

2. Apply the Measures to Filter Results

   • Use the previously created measures to filter the table visual.

   • You can create a new measure to combine the conditions for MCC1 and MCC2 and then filter the ClientID based on these conditions.

   • Measure for Filtering Clients:

     DAX

     FilteredClients = CALCULATE( VALUES(fact_transactions[ClientID]), FILTER( fact_transactions, [Transactions_MCC1] > 0 && [Transactions_MCC2] > 0 ) )

3. Add the FilteredClients Measure to the Visual Filter

   • Apply the FilteredClients measure as a filter to your table visual to display only the clients that meet the conditions.

By following these steps, you can set up a Power BI report that allows you to select multiple MCCs with specific amount ranges and filter the results to show only the clients that meet these conditions.