using summarize table columns in filter statement

[Dunner2020]

Hi there,

I am using summarize function to calculate the temporary table and then I want to calculate the rows count of that based on some condition. So I have written measure as follow:

Measure 1 = 
   

Var tempTable = SUMMARIZE('Table','Table'[Event ID],'Table'[Actual Event Start Time], 'Table'[EventType],'Table'[Status],"Distinct count",DISTINCTCOUNT('Table'[Actual Event Start Time ]))

RETURN

CALCULATE(

COUNTROWS(tempTable),

FILTER(tempTable,

tempTable[Actual Event Start Time (NZST)] >= [Current_RY_Yr_Strt] &&

'tempTable '[Actual Event Start Time (NZST)] < [Next_RY_Yr_Strt] &&

'tempTable '[ActualEvent Start Time (NZST)]<=Today() &&

'tempTable '[EventType]="3" && 'tempTable '[Status]="1"))

However, when I typed tempTable[Actual Event Start Time (NZST)] it underlines with red color and says can-not find [Actual Start Time (NZST).  Could anyone help me where I am making the mistake?

[v-robertq-msft]

Hi, @Dunner2020 

According to my test, if you used a variable to define a temporary table using Summarize() function, you can just enter”[” to get all the table you can use within the temporary table, and you can’t give the table name before the field name, like this:

Therefore, I suggest you to try to tranform your DAX formula like this:

Var tempTable = 
SUMMARIZE('Table','Table'[Event ID],'Table'[Actual Event Start Time], 'Table'[EventType],'Table'[Status],
"Distinct count",DISTINCTCOUNT('Table'[Actual Event Start Time ]))

RETURN

CALCULATE(

COUNTROWS(tempTable),

FILTER(tempTable,

[Actual Event Start Time] >= [Current_RY_Yr_Strt] &&

[Actual Event Start Time] < [Next_RY_Yr_Strt] &&

[ActualEvent Start Time]<=Today() &&

[EventType]="3" && [Status]="1"))

More info about Summarize() function in DAX

If this Dax formula still have a problem or it can’t achieve your requirement, you can post your sample pbix file (without sensitive data) and your expected result so that we can help in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Dunner2020]

@v-robertq-msft , thanks for your reply. I think we are almost there. I want to replace countrows with distinctcount. I want to perform calculation of distinctcount on [Actual Event Start Time] of tempTable. How could I apply the distinctcount on the tempTable?

[v-robertq-msft]

Hi, @Dunner2020 

According to your requirement, I think you can use the DistinctCount() function and SelectedColumns() function in DAX to transform your formula, you can try to change the measure like this:

Var tempTable =
SUMMARIZE('Table','Table'[Event ID],'Table'[Actual Event Start Time], 'Table'[EventType],'Table'[Status],
"Distinct count",DISTINCTCOUNT('Table'[Actual Event Start Time ]))
RETURN
CALCULATE(
DistinctCount(SelectedColumns(
FILTER(tempTable,
[Actual Event Start Time] >= [Current_RY_Yr_Strt] &&
[Actual Event Start Time] < [Next_RY_Yr_Strt] &&
[ActualEvent Start Time]<=Today() &&
[EventType]="3" && [Status]="1"),”1”,[Actual Event Start Time])

More info about the DistinctCount() function in DAX

If this Dax formula still have a problem or it can’t achieve your requirement, you can post your sample pbix file (without sensitive data) and your expected result so that we can help in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Dunner2020]

Hi @v-robertq-msft , thanks for the reply. DistinctCount does not accept SelectedColumns function as an argument and shows red colour line underneath the SelectedColumns function.

[amitchandak]

@Dunner2020 , In the temp table I can see [Actual Event Start Time] not [Actual Event Start Time (NZST)]

why single quotes 'tempTable ' ? why not 'tempTable 

Can you please check that.

Why this can not be done without summarize?

You can use filter while adding data to summarize

example

SUMMARIZE(FILTER('Table',
Table[Actual Event Start Time (NZST)] >= [Current_RY_Yr_Strt] &&
'Table'[Actual Event Start Time (NZST)] < [Next_RY_Yr_Strt] &&
'Table'[ActualEvent Start Time (NZST)]<=Today() &&
'Table'[EventType]="3" && 'tempTable '[Status]="1"))