topic NPS Score with a Filter (Different Table) in DAX Commands and Tips

NPS Score with a Filter (Different Table)

samdep — Wed, 02 Jun 2021 18:57:56 GMT

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!!

Re: NPS Score with a Filter (Different Table)

Fowmy — Wed, 02 Jun 2021 19:11:48 GMT

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" } )

Re: NPS Score with a Filter (Different Table)

samdep — Thu, 03 Jun 2021 15:59:35 GMT

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!