cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

tell me if you need something else

1 ACCEPTED SOLUTION
Super User

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 4
Super User

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

New Member

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

Super User

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

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.