Summarize table using a virtual table and Date Filter

[MrBrown]

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 

[Wilson_]

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?)

[MrBrown]

Hello Wilson,
this response is a partial answer to the topic, I think you understood my problem

But I would like to have a filter on the values "Yes", and "No"

Here an exemple 
https://drive.google.com/file/d/1drTUEuYLXup0t_vfdbZfh2IlH9k-ulf2/view?usp=sharing

[Wilson_ · Accepted solution]

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?)

[MrBrown]

thanks ! it's was exactly what i need! didn't know that we can use a measure as a slicer 🙂