Forum Discussion

samdep's avatar
samdep
Icon for Advocate II rankAdvocate II
5 years ago
Solved

NPS Score with a Filter (Different Table)

Hi All - 

 

Can anyone help me debug my code -- I'm building a time series and want to show NPS (all clients), NPS (Client Group 1), NPS (Client Group 2), etc. The Client Group designation lives on another table, but they are tied together in my model.

 

The expression provides the accurate net promoter score without a filter context, but once I throw that filter context on, nothing actually changes in terms of the score -- however, when I physically add the field as a filter on the visual and select 'Client Group 1', for example, I get the proper NPS for Client Group 1.

 

Since these are all going to be living on the same line graph, I'm trying to create a custom measure for each. Appreciate any advice - hopefully, it's something super simple that I'm just not catching being relatively new to the wonderful world of DAX! šŸ™‚

 

NPS Calc (Client Group 1) = 

VAR Promoter =

CALCULATE(COUNTA('Simple Survey'[MASTER NPS FIELD 1]), 'Simple Survey'[MASTER NPS FIELD 1] in {"Promoter"})
 

VAR Detractor  =

CALCULATE(COUNTA('Simple Survey'[MASTER NPS FIELD 1]), 'Simple Survey'[MASTER NPS FIELD 1] in {"Detractor"})
 
VAR All_NPS = 
CALCULATE(COUNTA('Simple Survey'[MASTER NPS FIELD 1]), 'Simple Survey'[MASTER NPS FIELD 1] in {"Detractor", "Promoter", "Passive"})
 

VAR prom_minus_detractor = promoter-detractor

 

RETURN

CALCULATE(DIVIDE(prom_minus_detractor , ALL_NPS), 'Clients'[Client_Group] in {"Client Group 1"})

 

Thank you in advance!!

  • Fowmy's avatar
    Fowmy
    5 years ago

    samdep 

    When you use Variables as Expressions inside CALCULATE, they are not evaluated by the filters in CALCULATE, variables are constant.Try the following modified  version:

    NPS Calc (Client Group 1) =
CALCULATE (
    DIVIDE (
        VAR Promoter =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Promoter" }
            )
        VAR Detractor =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor" }
            )
        VAR prom_minus_detractor = promoter - detractor
        RETURN
            prom_minus_detractor,
        VAR All_NPS =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor", "Promoter", "Passive" }
            )
        RETURN
            All_NPS
    ),
    'Clients'[Client_Group] IN { "Client Group 1" }
)

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    samdep 

    When you use Variables as Expressions inside CALCULATE, they are not evaluated by the filters in CALCULATE, variables are constant.Try the following modified  version:

    NPS Calc (Client Group 1) =
CALCULATE (
    DIVIDE (
        VAR Promoter =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Promoter" }
            )
        VAR Detractor =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor" }
            )
        VAR prom_minus_detractor = promoter - detractor
        RETURN
            prom_minus_detractor,
        VAR All_NPS =
            CALCULATE (
                COUNTA ( 'Simple Survey'[MASTER NPS FIELD 1] ),
                'Simple Survey'[MASTER NPS FIELD 1] IN { "Detractor", "Promoter", "Passive" }
            )
        RETURN
            All_NPS
    ),
    'Clients'[Client_Group] IN { "Client Group 1" }
)
    • samdep's avatar
      samdep
      Icon for Advocate II rankAdvocate II
      5 years ago

      This worked perfectly, thank you! I didn't realize what you'd said about variables being constant, so that's good to know - thank you!