Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.