Forum Discussion

kabra_ashish's avatar
kabra_ashish
Icon for Helper III rankHelper III
6 years ago
Solved

Slider - Does not take into consideration the data range selected

I have a date table with dates between Jan 1 2019 and Dec 31 2020. This date table is connected with a fact table which hosts the amounts and has a 1:Many relationship between them.

 

I created a slicer visual (slider) with the dates between Jan 1 2020 to February 11 2020 (using the data table). 

 

I need a sum of all the amounts using the above slicer. As it has a 1:many relationship I have used Calculate(sum(table.amount)) to sum up the values for the respective date.

 

Unfortunately it totals up all the data in the fact table. Any idea why does it total up all the values from the fact table and not the date range selected in  the slider?

 

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    This is because as you said in your orginal post, you do not want the audience to go to prior years data.

    So if you do not apply this filter to the card visual, when end users select the start date in 2019 in date slicer, the card will show the total sum of values including data in 2019.

    If you using other visual such as table visual, it is the same principle.

    For example, when you choose the start date from 2019/1/1, the table and card visual still show the data that start from 2020/1/1, Which will protect the data in 2019.

     

    Best Regards,

    Giotto Zhi

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    It should work if this is based column and date join in not inactive

    Calculate(sum(table[amount]))

     

    Can you share relation diagram and if amount is calculated field share its calculation

     

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    After my test, it works well here:

    So for your issue, please check your relationship.

    Please share some sample data and relationships you created and linked column if convenient for me to help you further.

     

    Best Regards,

    Giotto Zhi

     

    • kabra_ashish's avatar
      kabra_ashish
      Icon for Helper III rankHelper III
      6 years ago

      Thanks Giotto.

       

      The data model is exactly the same like what you have. Upload is disabled for me at the moment. I will try try to get the snapshot later.

       

      Here is the scenario:

      - The calendar table you have must have all the dates between Jan 1 2019 to Dec 31 2020.

      - The other table "Table" has dates and values for dates between Jan 1 2019 to March 10 2020.

      - Create a slider which shows dates from Jan 1 2020 to March 2020 (assume I want audience to see only current years data).

      - Can you tell me whether the sum of values you get when you have Jan 1 2020 to March 10 2020 selected in the data range is correct?

       

      For me when I select January 2 2020 to March 2020 in the slicer it shows the correct data. But when Jan 1 2020 to March 10 2020 is selected it actually sums up all the values in the table "Table" which means it includes data for 2019 as well. Basically the starting date is creating a problem for me.

       

      It is really strange to me. I must be missing some logic here. Any help is highly appreciated!

       

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi,

         

        Please check this:

        pbix 

        And could you please share your sum measure and relationships setting screenshot with me?

        Thanks!

         

        Best Regards,

        Giotto Zhi