Forum Discussion

arnomics's avatar
arnomics
Icon for Helper I rankHelper I
3 years ago

Flagging or Categorizing a Customer

Hi folks,   My firs time posting here & I'm new to DAX.   I'm struggling with this specific scenario & have spent hours on it. Hoping someone could help out.   I have a Table called Customer & ...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

You could create a separate measure for each category like

Category B =
VAR CategoryFilter =
    TREATAS (
        { ( 1, 1, 1, 0 ) },
        'Product'[Product A],
        'Product'[Product B],
        'Product'[Product C],
        'Product'[Product D]
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Subscriptions'[Customer ID] ), CategoryFilter )

You would need to specify a 0 or 1 for each column to make sure that, for example, people in category A didn't get double counted in the other categories.

arnomics's avatar
arnomics
Icon for Helper I rankHelper I
3 years ago

Thank you for your reply johnt75 

 

I gave this a try & the visual did not work. Not sure how to decode it.  The Value for Product Flags is in Text format (so 1 & 0) are in text format.

 

Thinking of an alernative solution & wondering if I could tag customers in some way i.e Customer 1 is tagged as A | B | C (as they have subscribed to Product A, B & C). 

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    You could try to add a category column like

    Customer Category =
VAR ProdA =
    SUMX (
        RELATEDTABLE ( Subscription ),
        VALUE ( RELATED ( 'Product'[Product A] ) )
    )
VAR ProdB =
    SUMX (
        RELATEDTABLE ( Subscription ),
        VALUE ( RELATED ( 'Product'[Product B] ) )
    )
VAR ProdC =
    SUMX (
        RELATEDTABLE ( Subscription ),
        VALUE ( RELATED ( 'Product'[Product C] ) )
    )
VAR ProdD =
    SUMX (
        RELATEDTABLE ( Subscription ),
        VALUE ( RELATED ( 'Product'[Product D] ) )
    )
VAR Result =
    SWITCH (
        TRUE (),
        ProdA >= 1
            && ProdB >= 1
            && ProdC >= 1
            && ProdD >= 1, "A|B|C|D",
        ProdA >= 1
            && ProdB >= 1
            && ProdC >= 1, "A|B|C",
        ProdA >= 1
            && ProdB >= 1, "A|B",
        "Default category"
    )
RETURN
    Result