Forum Discussion

EBoklund's avatar
EBoklund
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • Bijen_Shah's avatar
    Bijen_Shah
    2 years ago

    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!

  • Bijen_Shah's avatar
    Bijen_Shah
    2 years ago

    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's avatar
EBoklund
Frequent Visitor
2 years ago

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's avatar
Bijen_Shah
Regular Visitor
2 years ago

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!