Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Animated line chart show only one value

Dear friend I tried to use the "Play Axis" visual , it should display progressive with historical data but it only displays one value at a time. how can I fix it? what I need     
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    Create a disconnected table with the values of your play axis and then add the following measure to your line chart and use the new table to the Axis:

    TotalSales =
IF (
    SELECTEDVALUE ( 'YearPlayAxisTable'[Year] ) >= MAX ( 'Table'[Year] ),
    SUM ( 'Table'[Value] ),
    IF (
        COUNTROWS ( ALL ( 'YearPlayAxisTable'[Year] ) )
            <= COUNTROWS ( ALLSELECTED ( 'YearPlayAxisTable'[Year] ) ),
        SUM ( 'Table'[Value] )
    )
)

     

    If you want to keep the chart with all the year shown enter a measure like this:

    FlatLine = 0

     

    For this line set the line size has 0 and the data labels off

     

    Result below and in attach PBIX file:

     

    If you want you can change the flat line by another value to get it more dinamic and don't set the y-axis maximum and minimum.

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi Anonymous ,

 

Create a disconnected table with the values of your play axis and then add the following measure to your line chart and use the new table to the Axis:

TotalSales =
IF (
    SELECTEDVALUE ( 'YearPlayAxisTable'[Year] ) >= MAX ( 'Table'[Year] ),
    SUM ( 'Table'[Value] ),
    IF (
        COUNTROWS ( ALL ( 'YearPlayAxisTable'[Year] ) )
            <= COUNTROWS ( ALLSELECTED ( 'YearPlayAxisTable'[Year] ) ),
        SUM ( 'Table'[Value] )
    )
)

 

If you want to keep the chart with all the year shown enter a measure like this:

FlatLine = 0

 

For this line set the line size has 0 and the data labels off

 

Result below and in attach PBIX file:

 

If you want you can change the flat line by another value to get it more dinamic and don't set the y-axis maximum and minimum.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear MFelix, 

    I wonder if I can also do this for my historical data? The difference with my power bi report is that I use category x axis as "categorical data" instead of year. So I use a category of "100 days", "200 days", 300 days", so on. I followed the DAX above - but it seems it did not work because my data is in "categorical" instead of "continuous data". Could you help me with this? 

    I attached the power bi file here.  power bi file 

     

    Thank you so much. 

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      5 years ago

      Hi Anonymous ,

       

      Has I refered you need to have a disconnected table so in your case just do the following steps:

      • Delete the relationship between Try out and Date Category
      • Create the following measure:
      RunningTotalAxis = 
IF (
    SELECTEDVALUE ( 'Date category'[Category of date] ) >= MAX ( 'Try out'[Category of date] ),
    [Running Total],
    IF (
        COUNTROWS ( ALL ( 'Date category'[Category of date] ) )
            <= COUNTROWS ( ALLSELECTED ( 'Date category'[Category of date]) ),
       [Running Total]
    )
)
      • Format you line chart in the following way:
        • Axis: 'Try Out'[Category of Date]
          • Turn On: Show Items without Data
        • Values: [RunningTotalAxis]
      • If you want set the maximum Y-Axis value (I did it to 150 but you can use a measure)
      • Play axis visual:
        • 'Date category'[Category of date]

      On the image below the bottom chart is the one only using the Try out table for comparision and without interactions with the play axis.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Dear MFelix , 

        I have another question. So in my file, I have 4 line graphs in one spreadsheet. What I did in power BI for each graph is to filter it per category. But I realised that in the first line graph - the x-axis is missing "500 days" category. This may be because there is no data in that category. Is it possible to still show that category? I would like to show the x axis category as the ones in Digital events (100, 200, 300, 400, 500 and 600). 

         

        Thank you and I appreciate your help. 

        Best,

        Sherly

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    I made the same formula, but when I give play I don't join the lines