Forum Discussion

ailievsk1's avatar
ailievsk1
Regular Visitor
3 years ago

Filtering out consumers from a databse

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?

Need Help

4 Replies

    • ailievsk1's avatar
      ailievsk1
      Regular Visitor
      3 years ago

      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"? 

  • Adamboer's avatar
    Adamboer
    Responsive Resident
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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