cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Formula Alternative

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

1.PNG

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?

5 REPLIES 5
Highlighted
Super User
Super User

Re: Formula Alternative

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

mrainey Member
Member

Re: Formula Alternative

@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:

1.PNG

 

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.

Super User
Super User

Re: Formula Alternative

 

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

 





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Super User
Super User

Re: Formula Alternative

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

Super User
Super User

Re: Formula Alternative

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.