Forum Discussion

zausten's avatar
zausten
Helper I
4 years ago
Solved

Filter on Summarised table

Hi,

Is it possible to filter a summarised table based on a column or measure in the table ?

I want to apply a filter on summarizedtable to say where Measure 1 > 10 or the summarised Fact 1 > 10 .

I have tried to create a calculattable function on summarizedtable , but dont seem to be able to filter by  FACT1 and get a "placeholder" error using the measure. 

Basically this equivalent of doing  "having" in sql statement

 

Any ideas

thanks!

 

MyMeasure =

var dim1_type = SELECTEDVALUE(Table1[DIM1])

 

var fitleredtable = CALCULATETABLE(Table1, REMOVEFILTERS( Table1[DIM2]), Table1[DIM1] = dim1_type)

 

var summarizedtable = SUMMARIZE(fitleredtable, [DIM1], [DIM2], [FACT1], "Measure1", 'Measure Table'[Measure1])

 

var other stuff...

return ......

  • zausten , You filter like

     

    filter( SUMMARIZE(fitleredtable, [DIM1], [DIM2], [FACT1], "_Measure1", 'Measure Table'[Measure1]) ,[_Measure1] <10)

2 Replies

  • zausten , You filter like

     

    filter( SUMMARIZE(fitleredtable, [DIM1], [DIM2], [FACT1], "_Measure1", 'Measure Table'[Measure1]) ,[_Measure1] <10)

    • zausten's avatar
      zausten
      Helper I

      amitchandak 

      Wow that simple!  thanks for the tip.  looks like its working as expected. assume if you want to add multiple conditions you need to just wrap in multiple filter bys .
      e.g. 
      filter(filter( SUMMARIZE(fitleredtable, [DIM1], [DIM2], [FACT1], "_Measure1", 'Measure Table'[Measure1]) ,[_Measure1] >10),[_Measure1] <20)