Formula Alternative

[Anonymous]

I have 3 different formulas that are all built with this same logic:

So this formula does 3 different things:

1. Checks for the [Tier] a person is assigned to (based on their revenue)

2. Checks for their [NPS Level] (based on another formula)

3. Returns a dollar amount based on their [Tier] and [NPS Level]

While this works and gives me what I need, this formula makes my entire PBIX file run sluggish. The file takes a while to open, formatting things takes a while, connecting tables together can cause the PBIX file to freeze, etc. But as far as the formula itself goes, I've never built anything like this before so once I finally had a working solution I stuck with it. It's annoying because things that should take me 5 minutes to do now takes twice as long or even longer depending on what is because everything I click on needs time to load (if it doesn't freeze). I've deleted this formula a few times and rebuilt it to see if that's what was causing the issue and can confirm this is most likely the issue. Is there a different approach I can take to building something like this that won't slow down my dashboard?

[Stachu]

the format you showed is a bit difficult to work with, I was thinking about something like this (Power Query code)

Bonus

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUfILCFZwKsrPq0oFcoxNlWJ1EOLBnjllqUVAjrkBirh7fk4KkGloABE2xGGMIQ5jDNGMMYYIG6EbY44qDjcGXQPUHCOoc4zRzTFEk4AbhK4DapAxSDgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tier = _t, #"NPS Level" = _t, Bonus = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tier", Int64.Type}, {"NPS Level", type text}, {"Bonus", Int64.Type}})
in
    #"Changed Type"

then looking at your formula something like this should work (in DAX), refering to the Bonus table I posted above 

NPS Bonus = 
VAR varTier = 'EMS AM'[Tier]
VAR varNPS = [NPS Level]
RETURN
CALCULATE(MAX(Bonus[Bonus]),FILTER(Bonus,Bonus[Tier] = varTier && [NPS Level] = varNPS))

you may adjust the tables/column references to match you model though, without the model structure it's a bit difficult to be specific

[parry2k]

look at page 5 on attached, i created a tier commission table with following columns

tier

nps

amount

added a tierkey column which is concatenate of tier & nps

also created dummy tier data table and created the tierkey column and both are joined on tierkey

Also added a new column in teir data column called comm amount whch looks at tier commission table

feel free to reach out if you need clarification/help

[Stachu]

I'd suggest creating a reference table that would cover all the Scenarios
Tier|NPS|Bonus
0|NPS Bronze|35
0NPS Silver|70
...
and then look up the value for a specific Tier/NPS combination

[Anonymous]

@Stachu I've been suggested that before and really would like to take a stab at it, but I'm not familiar with working with looking up values from another table. I have a program table in place that looks like this that I've manually entered in the values for:

So I believe this would give me the start, I just don't know how to use it in conjunction with my data. I'm only using this for display purposes so people can see where the amounts are coming from. A lot of my confusion is that the formulas that decide a persons NPS, Retention and Satisfaction levels all come from different datasets.

[parry2k]

@Anonymoussend a sample data in excel and I will send you the solution