Values exist in two columns to create/filter to a new column

[LewisB]

I am trying create a new column that comfirms if a customer has bought product from both distributors. An example data set is below. Most companies only by one from distributor, however there are some that buy from both. I want to be able to filter out the custoemrs that buy from both distributors.

The columns I'm using below are 'Customer'[Customer Name], 'Distributor'[Distributor Name] and 'Key Measures'[Revenue].

These columns also exist in my fact table (called 'Orders'). eg.  'Orders'[Customer Name], 'Orders'[Distributor Name] etc.

I understand that this will most likely involve nested IF AND statements, but I am unable to get it to work!

Does anyone have tips to point me in the right direction?

[v-yingjl · Accepted solution]

Hi @LewisB ,

Sorry for replying late. You can create a measure liek this, put it in the visual filter and set its value as 0:

visual control =
IF (
    COUNTX (
        FILTER ( 'Key Measures', 'Key Measures'[Revenue] <> BLANK () ),
        [Customer]
    ) >= 2,
    1,
    0
)

 

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LewisB]

Would nested IF statements be better in this regard?

[LewisB]

I have tried the below formula, however it just shows up as "No" for all rows:

Both Distributors = SWITCH(

TRUE(),

Orders[Distributor Name] = "Distributor 1" && Orders[Distributor Name] = "Distributor 2" && [Revenue] > 0, "Yes",

"No"

)

I'm sure I'm missing a simple step here. Does it have something to do with the fact that both distributors are drawn from the same column in the same table?