Forum Discussion

MrBrown's avatar
MrBrown
New Member
3 years ago
Solved

Summarize table using a virtual table and Date Filter

Hello to all,

 

I'm stuck for 2 days on this little subject, I explain you,

I have a date filter on a range (between date1 and date2)

 


I also have a table that contains 3 columns : the date, an ID and a value (positive or negative)

like this:

 

-I would like to create a virtual table that calculates the sum of "Valeurs" grouped by Id

-Then from this dynamic table, I would like to create a column "Filter" If sum of "Valeur" = 0 by Id then I tag the row "Yes", otherwise "No"

This will allow me to see all the IDs that are not 0 (always on my chosen date range)

 

Note: if I change the date, the Yes, No can potentially change too

i tried something like this but when i filter the calculation isn't working : 

 

CalculateTable = 
    VAR StartDate = MIN(D00_Temps[Date])
    VAR EndDate = MAX(D00_Temps[Date])

    VAR TotalPerDay = FILTER(
        SUMMARIZE('Table',
                    'Table'[Id]
                    ,D00_Temps[Date]
                    ,"SumPerDay", SUM ('Table'[Valeurs])
                    
        ),D00_Temps[Date] >= StartDate && D00_Temps <= EndDate
    )

    Return TotalPerDay

 

 

thanks in advance for your help,

tell me if you need something else 

Interesting Links
Need Help
Tutorial Requests
  • Wilson_'s avatar
    Wilson_
    3 years ago

    MrBrown,

     

    Calculated tables cannot interact with slicers because they are not dynamic; they are created at dataset refresh/query time.

     

    However, what you can do is create the measure I shared above (if you didn't already have one), then follow the steps in this video. Using measures in a slicer is a commonly asked question so if that video doesn't work for you, there are a ton of other available resources out there. 🙂


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

4 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    3 years ago

    Hello MrBrown,

     

    Maybe I'm missing something but could you not just create a measure like:

    Filter =
SWITCH (
    SUM ( 'Table'[Valeur] ),
    0, "Yes",
    "No"
)

     

    Then you could create a table visual with Id, a slicer with your date, then filter your table visual for Filter = "Yes" only?

     

    If that doesn't work for you, can you share a link to a sample pbix?


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

      • Wilson_'s avatar
        Wilson_
        Memorable Member
        3 years ago

        MrBrown,

         

        Calculated tables cannot interact with slicers because they are not dynamic; they are created at dataset refresh/query time.

         

        However, what you can do is create the measure I shared above (if you didn't already have one), then follow the steps in this video. Using measures in a slicer is a commonly asked question so if that video doesn't work for you, there are a ton of other available resources out there. 🙂


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)