topic Re: How to create new calculated table and filter it dynamically using measures in DAX Commands and Tips

How to create new calculated table and filter it dynamically using measures

Bhargav150 — Fri, 12 Jul 2024 13:08:37 GMT

Hi I'm trying to create a new table based on existing dataset query1 in power bi, Because there is a duplicate values in the Snap_date column in the query1, But we need to use only Snap_date as filter so, that's why I have written a query like these

First created a date table

Date Table =

CALENDAR (

Min('query1' [Snap_date],

Max('query1' [Snap_date]

)

Then created 2 measures in the Date Table

Start Date = Min('Date Table'[Date]

End Date = Max('Date Table'[Date]

My requirement is to place the Date Table Date column in the between slicer and when I change the slicer these values need to go in to measures, Up to here everything working as expected

In the last step I have created a table

NewTable =

SUMMARIZE (

FILTER (

query1,

query1[SNAP_DATE] >= [Start Date] &&

query1[SNAP_DATE] <= [End Date]

query1[ID],

query1[LN],

query1[AMT],

query1[CITY]

)

When I change the values in the slicer why these date measures are not filtering data according to the slicer

Please help!

Re: How to create new calculated table and filter it dynamically using measures

some_bih — Mon, 15 Jul 2024 06:29:30 GMT

Hi Bhargav150 without model and details it is hard to spot issue.

Still, try v2 below

NewTable v2 =

VAR __Start_Date = Min('Date Table'[Date]

VAR __End_Date = Max('Date Table'[Date]

RETURN

SUMMARIZE (

FILTER (

query1,

query1[SNAP_DATE] >= __Start_Date &&

query1[SNAP_DATE] <= __End_Date

query1[ID],

query1[LN],

query1[AMT],

query1[CITY]

)

Re: How to create new calculated table and filter it dynamically using measures

Bhargav150 — Mon, 15 Jul 2024 07:14:06 GMT

Not working 😔 some_bih

Re: How to create new calculated table and filter it dynamically using measures

Anonymous — Mon, 15 Jul 2024 07:18:42 GMT

Hi Bhargav150 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new calculated table.

NewTable = SUMMARIZE ( query1, query1[SNAP_DATE], query1[ID], query1[LN], query1[AMT], query1[CITY] )

3.Create the new relationship between date table and calculated table.

4.Change the date range in the slicer visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to create new calculated table and filter it dynamically using measures

Bhargav150 — Mon, 15 Jul 2024 07:30:53 GMT

Hi Anonymous

FYI, the SNAPSHOT_DATE column updates daily with yesterday's date, but apart from that, the remaining columns' data is 90 percent similar. If I include SNAPSHOT_DATE in the summary, it will cause many duplicates. That's the problem here.

Re: How to create new calculated table and filter it dynamically using measures

Anonymous — Mon, 15 Jul 2024 09:05:40 GMT

Hi Bhargav150 ,

The slicer visual can’t influence the calculated table.

Besides, try to calculate the measure to filter the new table.

Measure = var _date = SELECTEDVALUE(NewTable[SNAP_DATE]) RETURN IF(_date >= [Start Date] && _date <= [End Date], 1, 0)

Then, set the show items is 1.

Viewing the following document to learn more information.

Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to create new calculated table and filter it dynamically using measures

theov — Tue, 18 Nov 2025 15:16:06 GMT

hey, check this out, it might help you: https://youtu.be/L9reA7CxfSg?si=esdvKGf-EZTlopQp

Re: How to create new calculated table and filter it dynamically using measures

Heavy1234 — Tue, 18 Nov 2025 16:42:38 GMT

Asegúrate de que exista una relación: Tabla de Fechas[Fecha] → query1[SNAP_DATE] Tipo: Many-to-One Dirección: Single o Both (según necesidad, Better is Single) Pon el segmentador en Tabla de Fechas[Fecha]. Luego NO necesitas medidas como filtros, porque los slicers ya filtran directamente query1.ón: Single o Both (según necesidad, Better is Single)

Pon el segmentador en Tabla de Fechas[Fecha].

Luego NO necesitas medidas como filtros, porque los slicers ya filtran directamente query1.