Forum Discussion
Filter and ALLEXCEPT not working
Resource Name,Month,RecordType,Actual,Flag Person A,1/1/2018,Project,100,1 Person A,1/2/2018,Support,40,1 Person A,1/3/2018,Training,50,1 Person A,1/4/2018,Unavailable,30,1 Person A,2/1/2018,Project,80,1 Person A,2/2/2018,Support,50,1 Person A,2/3/2018,Training,20,1 Person A,2/4/2018,Unavailable,50,1 Person A,3/1/2018,Project,150,1 Person A,3/2/2018,Support,10,1 Person A,3/3/2018,Training,20,1 Person A,3/4/2018,Unavailable,20,1 Person B,1/1/2018,Project,50,0 Person B,1/1/2018,Support,100,0 Person B,1/1/2018,Training,45,0 Person B,1/1/2018,Unavailable,5,0 Person B,2/1/2018,Project,90,0 Person B,2/1/2018,Support,80,0 Person B,2/1/2018,Training,10,0 Person B,2/1/2018,Unavailable,20,0 Person B,3/1/2018,Project,180,0 Person B,3/1/2018,Support,5,0 Person B,3/1/2018,Training,10,0 Person B,3/1/2018,Unavailable,5,0
I have the below dax where I get the daily percentage based on (month and year) But along with that I want to group by Resource name and record Type
which I am not getting
Current Filter along with this I want to group by Resource name and record Type
Total_Percentage = VAR TotalPerMonth = CALCULATE ( SUM ( data1[Actual] ), FILTER ( data1, data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) ), FILTER ( data1, data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) ), FILTER ( data1, data1[Flag] = 1 ) ) RETURN DIVIDE ( data1[actual], TotalPerMonth, 0 )
But I implemented below which is not working for me
Total_Percentage =
VAR TotalPerMonthRecordTypeResourceName =
CALCULATE (
SUM ( data1[Actual] ),
FILTER ( data1, data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) ),
FILTER ( data1, data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) ),
ALLEXCEPT(data1,data[RecordType],data1[Resource Name]),
FILTER ( data1, data1[Flag] = 1 )
)
RETURN
DIVIDE ( data1[actual], TotalPerMonthRecordTypeResourceName , 0 )
Hi vinaykva ,
It seems that you want to create the calculated colum. Please try the formula below.
You could use only one filter function that will give you the better performance.
Total_Percentage_ = VAR TotalPerMonthRecordTypeResourceName = CALCULATE ( SUM ( data1[Actual] ), FILTER ( 'data1', 'data1'[Record Type] = EARLIER ( 'data1'[Record Type] ) && 'data1'[Recource Name] = EARLIER ( 'data1'[Recource Name] ) && data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) && data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) && data1[Flag] = 1 ) ) RETURN DIVIDE ( 'data1'[Actual], TotalPerMonthRecordTypeResourceName )Your data sample may not look obvious, I add some rows to be obivously.
Best Regards,
Cherry
2 Replies
- v-piga-msft
Resident Rockstar
Hi vinaykva ,
It seems that you want to create the calculated colum. Please try the formula below.
You could use only one filter function that will give you the better performance.
Total_Percentage_ = VAR TotalPerMonthRecordTypeResourceName = CALCULATE ( SUM ( data1[Actual] ), FILTER ( 'data1', 'data1'[Record Type] = EARLIER ( 'data1'[Record Type] ) && 'data1'[Recource Name] = EARLIER ( 'data1'[Recource Name] ) && data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) && data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) && data1[Flag] = 1 ) ) RETURN DIVIDE ( 'data1'[Actual], TotalPerMonthRecordTypeResourceName )Your data sample may not look obvious, I add some rows to be obivously.
Best Regards,
Cherry
- v-piga-msft
Resident Rockstar
Hi vinaykva ,
Have you solved the problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry