Forum Discussion

arnomics's avatar
arnomics
Helper 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 & it contains Customer Number. This table is related to another Table called Subscriptions via Customer Number. The Subscription table connects to Products Table via a Product Key.

 

I'm trying to tag or categorise Customers based on Product Flags (4 different Prodcut Columns with values of 1 or 0). A 1 means the customer has subscribed to that Product. Some customers have subscribed to more than 1 Product (or a combination of Products).

 

I want to categorise customers based on below. So essentially, a unique Customer  can be categorised based on a unique combination of Products.

Category A =Product AProduct BProduct CProduct D
Category B =Product AProduct BProduct C 
Category C =Product AProduct B  

 

Output on a Table Visual would look like

 

CategoryCount of Customer
Category A =100
Category B =50
Category C =20

 

Table Names:

Customer: Columne Name = Customer Number

Product: Column Names = Product A, Product B, Product C & Product D. 

Values for each combination is either "1" or "0" depending on the subscription.

 

P.S. I'm using a Published dataset so cannot do a lot of things like Calculated Columns etc.

Need Help

3 Replies

  • johnt75's avatar
    johnt75
    Super 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
      Helper 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
        Super 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