Forum Discussion
using summarize table columns in filter statement
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 =
5 Replies
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"))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.
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?
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.
