Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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.
@Anonymoussend a sample data in excel and I will send you the solution
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |