CALCULATETABLE with SUMMARIZE and Dynamic Filter - Is this possible ?

[JoaqBI]

Hi All,

I am learning PowerBI and I am trying to create a table taking 3 columns from another table filtering the project number ( Information of another table) based on a selected value.
CurrentProj = SELECTEDVALUE(Proj_CostSummaries[Project_Number])

The Code to create the table is the following :

TimeSpent =

CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = [CurrentProj]))

I can see all the columns but for some reason the FILTER is not doing anything. If you can help me understand the issue that will be great!

[jaweher899]

It looks like the issue is that you are trying to use the selected value of Project_Number as a filter, but you are referencing it using the wrong syntax. In the FILTER function, you should reference CurrentProj directly instead of using the square brackets ([]) around it.

Try changing your code to the following:

TimeSpent = CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = CurrentProj))

[JoaqBI]

Thanks for your quick response, CurrentProj is defined like this :

CurrentProj = SELECTEDVALUE(Proj_CostSummaries[Project_Number])

If I try to change the FILTER with "00688" which is a possible value for Project Number it works 🙄

TimeSpent =

CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = "00688"))

I also tried to show CurrentProj in a card to make sure CurrentProj changes it's value based on the slicer. So I don't know why this is not working.

Thanks again!

[jaweher899]

create a measure that counts the rows that match the selected project number, and use that measure as a filter in your code.

CurrentProj = COUNTX(FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = SELECTEDVALUE(Proj_CostSummaries[Project_Number])))

and then use that measure in your filter.

TimeSpent = CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,CurrentProj >0))

[JoaqBI]

Thanks @jaweher899 but it seems it does not allow me to use CurrentProj without [].
I have also tried the code you suggested and had the following problems :