Count of a customer subcribed to 2 products

[Ani26]

Hi Team,

May be this is a very simple question but I am totally lost doing it and finally would require your help in achieving the result. I have a sample data as below.

Customer	Product
Ansh	Water
Prads	Fire
Kapil	Fire
Kapil	Water
Sam	Fire
Michael	Fire
Adnam	Water
Siko	Fire
Ted	Fire
Acqua	Water
Elies	Water
Board	Fire
Board	Water

The requirement is I need to find the distinct count of Customers who have subscribed to both Fire and Water together. So from the above example I should be getting Raj and Board as the 2 distinct customers who haved subscribed to both Fire and Water together.

Request you to kindly assist me with the same.

Please let me know if any additional information required.

Thanks,

Ani

[speedramps]

Hi Ani

Please conisder this solution and leave kudos

NO sales = COUNTROWS(Sales)                                                         -- number of sales rows
NO fire = CALCULATE(Sales[NO sales],Sales[Product]="Fire")            -- number of fire sales rows
NO water = CALCULATE(Sales[NO sales],Sales[Product]="Water")    -- number of water sales rows
NO customer with both = 
-- number of customers with both fire and water sales rows
CALCULATE(DISTINCTCOUNT(Sales[Customer]),
FILTER(Sales,[NO fire] > 0 && [NO water] > 0)
)

[camargos88]

Hi @Ani26 ,

Try this measure:

Measure =

CALCULATE(DISTINCTCOUNT('Table'[Customer]),

INTERSECT(

SELECTCOLUMNS(FILTER('Table', 'Table'[Product] = "Fire"), "Customer", 'Table'[Customer]),

SELECTCOLUMNS(FILTER('Table', 'Table'[Product] = "Water"), "Customer", 'Table'[Customer])

)

)

or

Measure =

COUNTROWS(

FILTER(

SUMMARIZE('Table', 'Table'[Customer], "QtProduct", CALCULATE(DISTINCTCOUNT('Table'[Product]), FILTER('Table', 'Table'[Product] = "Fire" || 'Table'[Product] = "Water"))),

[QtProduct] = 2)

)

[edhans]

This should do it.

Count Customers = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                Customers,
                Customers[Customer]
            ),
            "Combined Products", CALCULATE(
                CONCATENATEX(
                    Customers,
                    Customers[Product]
                )
            )
        ),
        [Combined Products] = "WaterFire"
        	|| [Combined Products] = "FireWater"
    )
)

EDIT: I did that really quick. I prefer things to be a bit more readable and modular. So I like the below better, but they both produce 2 as the answer.

Count Customers =
VAR TempTable =
    ADDCOLUMNS(
        SUMMARIZE(
            Customers,
            Customers[Customer]
        ),
        "Combined Products", CALCULATE(
            CONCATENATEX(
                Customers,
                Customers[Product]
            )
        )
    )
VAR JustFireWater =
    FILTER(
        TempTable,
        [Combined Products] = "WaterFire"
            || [Combined Products] = "FireWater"
    )
VAR FinalRowCount =
    COUNTROWS( JustFireWater )
RETURN
    FinalRowCount

[Ani26]

Thanks @edhans  and @camargos88 
Both the solutions helped me get to my answer. Thanks a lot 🙂