DAX SUMMARIZECOLUMNS with FILTER

[iwf]

Hi,

Would anybody be so kind and help me with this problem.

When I create this new DAX table:

Table2 = SUMMARIZECOLUMNS (

Activities_All[AcType];

Activities_All[ScheduledEnd].[Month];

Users[FullName]

"Test";SUM(Activities_All[CountActivities] ))

It will give me the the Count of CountActivities for each unique combination of AcType; Month;FullName

but if I add this Filter

Table2 = SUMMARIZECOLUMNS (

Activities_All[AcType];

Activities_All[ScheduledEnd].[Month];

Users[FullName]

;FILTER( Users;[Position]= "Analyst" || Users[Position] = "Sales");

FILTER( Activities_All;[ScheduledEnd] > EOMONTH(TODAY();-3) && [ScheduledEnd] < EOMONTH(TODAY();-1)); "Test";SUM(Activities_All[CountActivities] ))

The SUM stops to work. It only "merge" AcType with FullName. The column ScheduledEnd shows the dates (without grouping).

Thank you for help

[v-frfei-msft]

Hi @iwf,

I made a sample as below using your formula, I can get the sum of CountActivities of the filtered rows.

Table3 = SUMMARIZECOLUMNS (
Activities_All[AcType],
Activities_All[ScheduledEnd].[Month],
Users[FullName],FILTER(Users,Users[Position]= "Analyst" || Users[Position] = "Sales"),
FILTER( Activities_All,Activities_All[ScheduledEnd] > EOMONTH(TODAY(),-3) && [ScheduledEnd] < EOMONTH(TODAY(),-1)), "Test",SUM(Activities_All[CountActivities] ))

And my original tables are like this.

For more details, please check the pbix as attached. If it does not meet your requirement, kindly share your excepted result to me.

https://www.dropbox.com/s/75oi3i4teyx9sf0/DAX%20SUMMARIZECOLUMNS%20with%20FILTER.pbix?dl=0

Regards,

Frank

[iwf]

Frank,

thank you for your answer. Is it possible, that the output in TABLE2 in column Month would be Date format? It is always a Text. What I wanted to achieve is something like this:

https://powerbi.tips/2016/07/measures-month-to-month-percent-change/

To compare the change between 2 previous month. So I thought that I would make one calculated table. But I ended with the text field.

Finally this solution worked for me:

I create First table for Pre-previous month

PrevTwoTable = SUMMARIZECOLUMNS(Activities_All[AccType];FILTER( Activities_All;[ScheduledEnd] > EOMONTH(TODAY();-3) && [ScheduledEnd] < EOMONTH(TODAY();-2));"PreviousTwo";SUM(Activities_All[Count CRM Activities]))

Then I create the same table but for previous month and add measure column

PrevOneTable = SUMMARIZECOLUMNS(Activities_All[AccType];FILTER( Activities_All;[ScheduledEnd] > EOMONTH(TODAY();-2) && [ScheduledEnd] < EOMONTH(TODAY();-1));"PreviousOne";SUM(Activities_All[Count CRM Activities]))

+ new column

Column = LOOKUPVALUE(PrevTwoTable[PreviousTwo];PrevTwoTable[AccType];PrevOneTable[Wood Type])

I ended with two tables, but it is working. Maybe it is not nice solution, but it is working

Thanks