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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EBoklund
Frequent Visitor

DAX causing problems calculating dynamic indices base 100

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

EBoklund_0-1708077090845.png

 

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

EBoklund_1-1708077149262.png

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.

EBoklund_2-1708077196268.png

 

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

EBoklund_3-1708077411121.png

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.

EBoklund_4-1708077644833.png

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

EBoklund_5-1708077736577.png

 

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.

2 ACCEPTED SOLUTIONS
Bijen_Shah
Regular Visitor

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!

View solution in original post

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.

Bijen_Shah_0-1708341831096.png


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!

View solution in original post

8 REPLIES 8
HotChilli
Super User
Super User

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?

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
Regular Visitor

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!

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.

EBoklund_0-1708340161456.png

 

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.

Bijen_Shah_0-1708341831096.png


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!

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!

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.

EBoklund_0-1708347335202.png

 

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

 

Bijen_Shah_0-1708350950912.png


Here is a screenshot of the visual.

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



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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