DAX causing problems calculating dynamic indices base 100

[EBoklund]

I have a problem, that I need help with. I work with BI for a larger Danish labour union, and I'm working on a PBI report, in which I intend to calculate dynamic indices of the membership count where base 100 is 2021. Basically, the end product should be a dynamic version of the combined bar and line chart, like the one shown below (old Excel version of the report, which I need to make in Power BI):

The bars shows a local department and the line the union total for comparison. What I want the report to do is to recalculate the  index using 2021 as base 100, so that when a slicer choice is made (two slicers, department and membership type), it recalculates everything dynamically. I tried using the solution from this query, but it doesn't quite get me there.

I created two queries: One has 4 columns: Local department, Membership type, Date and Member count. Note that the dates are all January 1st in a given year (2017-2024).

The other one is basically the same, but without the Local departments, so instead it gives me the count for the union as a total.

I used this DAX command to calculate the indices for the local depts.:

Index Local dept = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN MAX ( 'Index local'[Member count] ) 
/ CALCULATE ( SUM ( 'Index local'[Member count] ), 'Index local'[Date] = Date_index ) * 100 

And a similar one to calculate the union index:

Index Union = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN MAX ( 'Index Union total'[Member count] ) 
/ CALCULATE( MAX ( 'Index Union total'[Member count] ), 'Index Union total'[Date] = Date_index ) * 100 

Finally, I created a calendar table using 

Calendar index = calendarauto(12) 

As per the linked solution, I created a calendar slicer with no relations, but filtered it down to 01-01-2021 and made it invisible (as it needs to be set to 2021 always and  shouldn't be tampered with).

It almost works. However, I'm having two major problems:

1. When I make a slicer choice to a local dept. and either membership type, it works just fine, and the index calculations are spot on as per my control sheet in Excel, calculates 2021 to base 100 and the other years accordingly. But when I choose both mebership types (or none, same case), the index base is not recalculated to 100, but instead shows some oddly low numbers.

2. When I add the line chart with the Union index, it goes totally bonkers. 2021 is calculated correctly as base 100, but the indices at all other dates are infinite (billions):

So what is going wrong here, and how do I achieve what I want? I'm almost at my wits end.

Thanks in advance for any help.

[Bijen_Shah · Accepted solution]

I think if you try changing the aggregation functions MAX instead use SUM.
From the suggestion you have shared - I think if you make these changes your output will change - as when you select multiple member types it has to get the sum instead of maximum value.

Index Local dept = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN SUM ( 'Index local'[Member count] ) 
/ CALCULATE ( MAX( 'Index local'[Member count] ), 'Index local'[Date] = Date_index ) * 100 

Index Union = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN SUM( 'Index Union total'[Member count] ) 
/ CALCULATE( MAX ( 'Index Union total'[Member count] ), 'Index Union total'[Date] = Date_index ) * 100 

 
@EBoklund 
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos!! Happy to Help!

[HotChilli]

Can you provide a sample pbix please?  It will be much quicker to find out what is going on.

--

At the moment, we can't see what the model is (2 fact tables, disconnected date table?).  What's in the bar chart?  What fields are in the slicers?

[EBoklund]

Here you go.

Please note, that part one was solved, so what remains is the bar chart, which still doesn't compute the index calculations correctly, although it's no longer infinite numbers (see my reply below).

Get the sample file here (packed with WinRAR): https://www.dropbox.com/scl/fi/zjsha02n12i4urike5agk/Sample-report.rar?rlkey=eenpdeiydrbsjai57lilfuq...

[Bijen_Shah · Accepted solution]

I think if you try changing the aggregation functions MAX instead use SUM.
From the suggestion you have shared - I think if you make these changes your output will change - as when you select multiple member types it has to get the sum instead of maximum value.

Index Local dept = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN SUM ( 'Index local'[Member count] ) 
/ CALCULATE ( MAX( 'Index local'[Member count] ), 'Index local'[Date] = Date_index ) * 100 

Index Union = 
VAR Date_index = MIN ( 'Calendar index'[Date] ) 
RETURN SUM( 'Index Union total'[Member count] ) 
/ CALCULATE( MAX ( 'Index Union total'[Member count] ), 'Index Union total'[Date] = Date_index ) * 100 

 
@EBoklund 
Did I answer your question? Mark my post as a solution!
Appreciate your Kudos!! Happy to Help!

[EBoklund]

Thank you! It solved the first part of my problem. The second part (the line graph for the union total) is still off the charts, though, albeit no longer infinite. I found out that the infinity problem was caused by PBIs autorelation function. I removed the automated relations, but it still doesn't index correctly for the line chart.

[Bijen_Shah · Accepted solution]

Hi @EBoklund ,

I have not had a look at the pbix you shared but I am sure you are facing the line issue because it does not have row context to it.

I tried creating the same in a blank file and i am able to see indexing fine.

I think what you need to do is create a calendar table which is related to both your aggregated tables and then only use the dates from that calendar table on the visual - this way both your table's measures / columns you use will have filter context on what dates are queried on the visual.
Note, this calendar table is different than your static choice of index date "01/01/2021".

For now, to prove my point if you try to change your selected date column in chart from one table to another your bar or line data will not give you the expected result.


Did I answer your question? Mark my post as a solution!
Appreciate your Kudos!! Happy to Help!

[EBoklund]

And bingo! I think I found out what was wrong: After relating to the new calendar table, I needed to change the date reference in my two DAX sentences, so that it used the calendar table date instead of the query dates. It seems to work now, just need to check it with my control calculations! Thanks a bunch, you've been a great help!

[EBoklund]

I see your point. However, when I do the suggested - create a new calendar, relate it to both of the queries' data columns, and use that date on the x-axis, it causes both the bars AND the line to go bonkers again and return infinite numbers, except for the index base year.

[Bijen_Shah]

Hi @EBoklund ,

I have uploaded a pbix in here.

Sample report.pbix
The fix was to use the Count Dates [Date] column in the measures and also on the visual.

Index dept = VAR Date_index =
    MAX ( 'Calendar index'[Date] )
RETURN
    SUM ( 'data local'[Member count])
        / CALCULATE( SUM ( 'data local'[Member count] ), 'Count dates'[Date] = Date_index )
        * 100

Index union = VAR Date_index =
    MAX ( 'Calendar index'[Date] )
RETURN
    SUM ( 'data union'[Member count] )
        / CALCULATE( SUM ( 'data union'[Member count] ), 'Count dates'[Date] = Date_index )
        * 100

Here is a screenshot of the visual.

Did I answer your question? Mark my post as a solution!
Appreciate your Kudos!! Happy to Help!