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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
marchn
Helper I
Helper I

DAX improvement or decline in scores over months

Hello

I'm looking for some help with DAX. I have a table in Power BI which lists organisations, their assigned relationship type (Partner, Engaged, Interested, Neutral, Critic), a calculated score based on the relationship type (e.g. Partner = 5, Engaged =4 etc.) and then dates that the snapshot of data was taken. Therefore, each organisation will appear more than once, because in May they had one relationship type and then it may (or may not) have changed in July and August.

I want to create a visuals which show the number of organisations to have improve, declined or remained static with their relationship types or scores between any two periods chosen by the user. For instance, if an organisation goes from Neutral (2) to Interested (3), then they have improved. If they go from Partner (5) to Engaged (4) then they have declined. I have two slicers based on two calendar tables - the comparison table has an inactive relationship and I've managed to use this on other visuals doing different types of comparisons.

Can anyone help me with the DAX to achieve the above scenarios?

 

 

Org IDOrg NameRelationship TypeRelationship ScoreDate
1CostcoNeutral231/05/2023
2WH Smith 031/05/2023
3StarbucksCritic131/05/2023
4WilkosInterested331/05/2023
5Pizza Hut 031/05/2023
6McDonald's 031/05/2023
7KFCCritic131/05/2023
8BootsNeutral231/05/2023
9ArgosCritic131/05/2023
1CostcoNeutral230/06/2023
2WH SmithCritic130/06/2023
3StarbucksCritic130/06/2023
4WilkosInterested330/06/2023
5Pizza HutNeutral230/06/2023
6McDonald'sCritic130/06/2023
7KFCCritic130/06/2023
8BootsNeutral230/06/2023
9ArgosCritic130/06/2023
1CostcoCritic130/07/2023
2WH SmithCritic130/07/2023
3StarbucksInterested330/07/2023
4WilkosNeutral230/07/2023
5Pizza HutNeutral230/07/2023
6McDonald'sNeutral230/07/2023
7KFCNeutral230/07/2023
8BootsInterested330/07/2023
9ArgosNeutral230/07/2023
10River Island (new org)Critic130/07/2023
1CostcoNeutral231/08/2023
2WH SmithNeutral231/08/2023
3StarbucksPartner531/08/2023
4WilkosInterested331/08/2023
5Pizza HutInterested331/08/2023
6McDonald'sEngaged431/08/2023
7KFCNeutral231/08/2023
8BootsNeutral231/08/2023
9ArgosInterested331/08/2023
10River Island (new org)Neutral231/08/2023

 

1694690225940.png1694690317921.png

Example in Excel of what I'm trying to achieve from the visual - using different data.

1 ACCEPTED SOLUTION

Hi

I've been trying for a couple of days to get the DAX you suggested to work and to try and understand what the DAX is doing (which I'm failing at), but for some reason it is just returning blank results and I'm not sure what I'm doing wrong. Perhaps it's my relationships between tables. I have to have the comparison calendar so that other functionality works. I have pasted what I have done below. Some of the field names are slightly different to yours, but I think I've selected correctly. Any ideas what I might be doing wrong?

 

Results - blank for delta

 

marchn_0-1695204206766.png

My DAX

marchn_1-1695204243957.png

I've temporarily removed the relaltionship with the inactive comparison calendar just to see if that was the problem:

marchn_2-1695204378327.png

 

View solution in original post

18 REPLIES 18
lbendlin
Super User
Super User

Here is the first step - a slight cleanup of the data model

lbendlin_0-1694899922979.png

 

Then the next step is to calculate the trend for each org.

lbendlin_1-1694899957086.png

 

Now for your Relationship type change expected outcome you need to indicate over which time interval you want that.  Month by month or cumulative?

 

 

Hi

I've been trying for a couple of days to get the DAX you suggested to work and to try and understand what the DAX is doing (which I'm failing at), but for some reason it is just returning blank results and I'm not sure what I'm doing wrong. Perhaps it's my relationships between tables. I have to have the comparison calendar so that other functionality works. I have pasted what I have done below. Some of the field names are slightly different to yours, but I think I've selected correctly. Any ideas what I might be doing wrong?

 

Results - blank for delta

 

marchn_0-1695204206766.png

My DAX

marchn_1-1695204243957.png

I've temporarily removed the relaltionship with the inactive comparison calendar just to see if that was the problem:

marchn_2-1695204378327.png

 

You can use the filter pane to specify any number of months that you like (including two). The formula will work either way (you may have to replace REMOVEFILTERS with ALLSELECTED() ).

Hi

 

I really appreciate you taking the time to help me. I have replaced REMOVEFILTERS with ALLSELECTED, but the Delta field is still showing up as blank. I've tried creating a completly new query from scratch and using a simplified date table, but that also doesn't work. Any ideas what I might be doing?

you might be overcomplicating this

 

 

Hello - thank you for attaching your version. I can see it's working in your version. So I recreatd my version to completely mimic yours, but mine still doesn't work. I have no idea why. Perhaps I have a setting wrong. With the DAX, the only thing that is different for me is that I have ' ' around the table names. I'm not sure if this make a difference, or if the source my query is based on caused a difference.  Or if I'm on a different version of PowerBI. When I opened your query, I noticed that it stated the saved query was created in a newer version of PowerBi. 

 

marchn_0-1695654917117.png

 

The single quotes are only required if the table name has spaces. 

 

Can you confirm that your "Date"  table is actually one? Did you mark it as such?

Thank you. It wasn't previously marked as a date table, but I have now marked it as the date table. 

Hi

 

Here is my link to my PowerBI - hopefully this will work. If you're able to see what I've done wrong, that would be really helpful. The real query I have to get this to work on is larger, with far more data and more complex, but one step at a time I guess. Thank you. 

 

Practice_Scores2.pbix

You used the wrong date column.  Needs to come from the Dates table

Oh my goodness!!!! I can't believe it! that's it! I knew it had to be something simple like that, that I have been missing. Thank you so much for perservering with me. I really appreciate it. 

Apologies for adding more questions - but now I've managed to get this working, is there a way that I can allow my users to select the two months they want to compare? So if they want to compare June with August,  or May with August etc, so the deltas change depending on what the user selects?

That is already built-in.  use a slicer to let them choose the months. Educate them that it won't work if they pick less than or more than two values.

Oh yes - I can see I need to press ctrl to select more than one, or uncheck that option in settings. 

Hopefully this is my final step - but now that I have achieved this, I want to be able to count how many organisations have a positive delta (meaning they have improved their score), negativel delta (meaning they have declined) or zero delta (meaning they have stayed static between the two periods selected). I suppose a "countif" approach if it were in Excel, so I can display the counts similar to the below. Thank you so much for your help - I've been totally stumped by this one, but I'm learning so much. 

 

marchn_0-1695756175160.png

 

One small advice if I may - forget what you have learned in Excel.  (At least for this task).  Embrace Power BI's capabilities, don't try to make it into something it isn't.

 

Here's one of many options:

 

lbendlin_0-1695767093797.png

 

 

 

This works perfectly. You're a genius - thank you. I can see I really need to learn how to use variables and the syntax for Power BI - so thank you for your advice. 

 

I have one other question - perhaps I need to ask it in a new thread. I would like to have a gauge or a KPI which shows the percetage of "improved" organisations between the two selected dates - I have a target of improving x% (let's say 50%) of organisations by 31 March, is there a way of showing the progress towards this target on a gauge or KPI visual? I realise that I would need to select the dates as the period I have to achieve the target. 

Hi thank you so much for taking the time to reply. I will make a start on your first steps you have proposed. 

 

I would like my comparison to be month-by-month, so the user could choose any two months to compare. This is why I have two calendars in my model, because I have some other visualisations where one slicer is based on "calendar" and the other is based on the inactive "comparison calendar". In my actual dataset I have an organisation list, so I presume I can make the link between org IDs there. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors