Forum Discussion

BoatAnalytics's avatar
BoatAnalytics
Frequent Visitor
11 months ago
Solved

Creating Customer Profile, Switch formula based off product 0 or 1 criteria

Hi,

 

I am stuck trying to figure this Switch and data model out. I think I am close, but not working the way I want. 

 

I have 4 Calculated columns that count either a 0 or 1. If there is an exisitence of the product in the Customers ID in my Fact Table. There are multiple entries for the Customer. When I applied this Customer Profile Logic it gives me multiple Entries and I only want one. 

 

I think the Calculated columns are not pulling the Max for that specific Customer ID if there is exisitence of that product just that line. Thats why I am suspecting it is giving me multiple Customer Profiles. 

 

Curious if anyone had any ideas on this. 

 

Thanks for your help!

 

Measure to get data's Max of each category (If exisistence of the product (1) or not (0))

Example: 
If Target = SUMX(
    SUMMARIZE(
        'Billing ID',
        'Billing ID'[Boat ID],
        "MaxValue", Max('Billing ID'[IF Target])
    ),
    [MaxValue]
)

 

Calculated Column

CustomerGroup =
SWITCH(
    TRUE(),[If Tenant] = 0 && [If Annual Stores] = 0 && [If Target Add] = 1 && [If Labor] = 1  , "Target Store & Service",
           [If Tenant] = 0 && [If Annual Stores] = 0 && [If Target Add] = 1 && [If Labor] = 0 ,  "Target Store & Don't Service",
           [If Tenant] = 0 && [If Annual Stores] = 0 && [If Target Add] = 0 && [If Labor] = 1  , "Target Don't Store & Service",
           [If Tenant] = 0 && [If Annual Stores] = 0 && [If Target Add] = 0 && [If Labor] = 0  , "None",
    "Other"
)

 

 

For One Boat ID

 

 

Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    11 months ago

    Hi BoatAnalytics,

     

    Reason for false results is a single Boat ID (customer) has multiple rows, those flags can vary row by row → which means your SWITCH in CustomerGroup is also returning multiple classifications for the same customer.

     

    Solution is instead of adding column to original table you can create a table as below

     

    CustomerProfiles =
    SUMMARIZE (
    'Billing ID',
    'Billing ID'[Boat ID],
    "IfTenant", MAX ( 'Billing ID'[If Tenant] ),
    "IfAnnualStores", MAX ( 'Billing ID'[If Annual Stores] ),
    "IfTargetAdd", MAX ( 'Billing ID'[If Target Add] ),
    "IfLabor", MAX ( 'Billing ID'[If Labor] )
    )

     

    then you can add a calculated column to this table 

     

    CustomerGroup =
    SWITCH (
    TRUE(),
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 1 && [IfLabor] = 1, "Target Store & Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 1 && [IfLabor] = 0, "Target Store & Don't Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 0 && [IfLabor] = 1, "Target Don't Store & Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 0 && [IfLabor] = 0, "None",
    "Other"
    )

     

    Alternatively you can create measures for all the columns taking max out of it (if you want to avoid creating additional table)

     

    IfTenant_Measure =
    MAX ( 'Billing ID'[If Tenant] )

    IfAnnualStores_Measure =
    MAX ( 'Billing ID'[If Annual Stores] )

    IfTargetAdd_Measure =
    MAX ( 'Billing ID'[If Target Add] )

    IfLabor_Measure =
    MAX ( 'Billing ID'[If Labor] )

     

    and then use above measure into your final measure

    CustomerGroup =
    SWITCH (
    TRUE(),
    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 1 && [IfLabor_Measure] = 1, "Target Store & Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 1 && [IfLabor_Measure] = 0, "Target Store & Don't Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 0 && [IfLabor_Measure] = 1, "Target Don't Store & Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 0 && [IfLabor_Measure] = 0, "None",

    "Other"
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

2 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    11 months ago

    Hi BoatAnalytics,

     

    Reason for false results is a single Boat ID (customer) has multiple rows, those flags can vary row by row → which means your SWITCH in CustomerGroup is also returning multiple classifications for the same customer.

     

    Solution is instead of adding column to original table you can create a table as below

     

    CustomerProfiles =
    SUMMARIZE (
    'Billing ID',
    'Billing ID'[Boat ID],
    "IfTenant", MAX ( 'Billing ID'[If Tenant] ),
    "IfAnnualStores", MAX ( 'Billing ID'[If Annual Stores] ),
    "IfTargetAdd", MAX ( 'Billing ID'[If Target Add] ),
    "IfLabor", MAX ( 'Billing ID'[If Labor] )
    )

     

    then you can add a calculated column to this table 

     

    CustomerGroup =
    SWITCH (
    TRUE(),
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 1 && [IfLabor] = 1, "Target Store & Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 1 && [IfLabor] = 0, "Target Store & Don't Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 0 && [IfLabor] = 1, "Target Don't Store & Service",
    [IfTenant] = 0 && [IfAnnualStores] = 0 && [IfTargetAdd] = 0 && [IfLabor] = 0, "None",
    "Other"
    )

     

    Alternatively you can create measures for all the columns taking max out of it (if you want to avoid creating additional table)

     

    IfTenant_Measure =
    MAX ( 'Billing ID'[If Tenant] )

    IfAnnualStores_Measure =
    MAX ( 'Billing ID'[If Annual Stores] )

    IfTargetAdd_Measure =
    MAX ( 'Billing ID'[If Target Add] )

    IfLabor_Measure =
    MAX ( 'Billing ID'[If Labor] )

     

    and then use above measure into your final measure

    CustomerGroup =
    SWITCH (
    TRUE(),
    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 1 && [IfLabor_Measure] = 1, "Target Store & Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 1 && [IfLabor_Measure] = 0, "Target Store & Don't Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 0 && [IfLabor_Measure] = 1, "Target Don't Store & Service",

    [IfTenant_Measure] = 0 && [IfAnnualStores_Measure] = 0 &&
    [IfTargetAdd_Measure] = 0 && [IfLabor_Measure] = 0, "None",

    "Other"
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

    • BoatAnalytics's avatar
      BoatAnalytics
      Frequent Visitor
      11 months ago

      Excellent solution! Thanks so much, worked exactly how I was hoping. 

       

      Thanks again!