Forum Discussion

Arnoux's avatar
Arnoux
Frequent Visitor
6 years ago
Solved

Filter dataset before applying summarize (create new table ) statement

I have a data model in PowerBI which consists of a Fact Table and a dimesnion

For simplicity sake ill call it Fact and Dimension.

Fact:

FactIDDimesnionIDValueDateTime
1101/1/2019 08:01
2112/1/2019 08:02
3213/1/2019 08:03
4114/1/2019 08:15
5315/1/2019 08:30
6306/1/2019 08:45
7317/1/2019 08:00

 

Dimesnion

 

DimensionIDName
1A
2B
3C
4D
5E
6F
7G

The relationship between these 2 tables are dimesnionid on dimensionid

 

Now I need to create a Summarize statement like the following:

Table =
SUMMARIZE (
    'Fact',

    'Dimension'[Name],
    "Value",SUM('Fact'[Value])

)

 

 

Now this seems to work as a once off.

 

But now when i filter the date on the front end in a filter. The values of the calculated table does not adjust according to the filter.

 

So ideally I would like a solution where the summarize statement is based on the filtered set of data on the fact and not the full dataset.

 

So When i filter the datetime on the report. The table must recalculate based on a new subset of Fact Records.

 

Im stuck.

 

 

Please help.

 

Arnoux

  • Arnoux ,

     

    Power bi doesn't support dynamic calculate column/table so I'm afraid this can't be achieved. Filters/Slicer charts are in view level while the summarized table is in model level, in power bi, view level can't filter data in model level.

     

    Community Support Team _ Jimmy Tao

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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    Arnoux ,

     

    Power bi doesn't support dynamic calculate column/table so I'm afraid this can't be achieved. Filters/Slicer charts are in view level while the summarized table is in model level, in power bi, view level can't filter data in model level.

     

    Community Support Team _ Jimmy Tao

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