Forum Discussion

Bhargav150's avatar
Bhargav150
Frequent Visitor
2 years ago
Solved

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

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 filte...
"DAX help"
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

Bhargav150's avatar
Bhargav150
Frequent Visitor
2 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • Heavy1234's avatar
      Heavy1234
      Regular Visitor
      9 months ago

      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.