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

EBoklund's avatar
EBoklund
Frequent Visitor
2 years ago

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's avatar
    Bijen_Shah
    Regular Visitor
    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!



    • EBoklund's avatar
      EBoklund
      Frequent Visitor
      2 years ago

      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!