Filtering out consumers from a databse

[ailievsk1]

Hello everyone!

I have two databases:

1. in which i have listed all customers with customer code - PBI_Consumer_SEE'[GF_Referral_Code]

2. customer codes which i actually need to see in the report (which also consist in databse #1) - Participants'[Customer Code]

How can i match them using filter function?

I tried this DAX: 

CALCULATE(COUNT('PBI_Consumer_SEE'[GF_Referral_Code]),ALL('Participants'[Customer Code]))

Can you please help me out?

[v-cgao-msft]

Hi @ailievsk1 ,

If you want a calculated table.

Table =
CALCULATETABLE (
    DISTINCT ( 'PBI_Consumer_SEE'[GF_Referral_Code] ),
    FILTER (
        ALL ( 'PBI_Consumer_SEE' ),
        'PBI_Consumer_SEE'[GF_Referral_Code] IN VALUES ( 'Participants'[Customer Code] )
    )
)

If you want to do some filtering in the table visual. You can use the measure provided by amitchandak and drag it into the visual filter and set it greater than 0.

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

[Adamboer]

To match the customer codes from the two databases, you can use the FILTER function in DAX. Here is an example of how to use it: CALCULATE( COUNT('PBI_Consumer_SEE'[GF_Referral_Code]), FILTER( 'PBI_Consumer_SEE', 'PBI_Consumer_SEE'[GF_Referral_Code] IN VALUES('Participants'[Customer Code]) ) )

n this formula, the FILTER function is used to select only the rows in the 'PBI_Consumer_SEE' table where the GF_Referral_Code is included in the list of customer codes in the 'Participants' table. The VALUES function is used to return the distinct list of customer codes from the 'Participants' table. Finally, the COUNT function is used to count the number of matching rows in the 'PBI_Consumer_SEE' table.

You can adjust this formula to fit your specific needs, but using the FILTER function in this way should allow you to match the customer codes from the two databases.

[amitchandak]

@ailievsk1 , Try like

Based on what I got , you need measure like

CALCULATE(COUNT('PBI_Consumer_SEE'[GF_Referral_Code]),Filter( PBI_Consumer_SEE',  PBI_Consumer_SEE'[GF_Referral_Code] in values( 'Participants'[Customer Code])))

or

use https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[ailievsk1]

Thanks a lot @amitchandak , in fact, i don't need the result in values, i just need to see the needed customer codes listed in a table. That's why i need the measure. 

What should i change instead of "in values"?