Combining AllSELECTED with ALLEXCEPT

[Frajzerr]

Hello,

Let's say I have a visual table like below and my problem regards to AvgNumber measure.

To make it simple I'am going to split the problem into a few steps.

First visual(table) below shows AvgNumber as basic Average(number) 

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	10
A	FG	15.01.2022	20	20
C	GG	15.05.2022	50	50
D	GT	16.07.2022	40	40

What I want to do is remove visual filter context from Date column, but keep context of Product and Type. I did it using Calculate + Allexcept, Calculate(average(number), allexcept(Table,Product,Type)), results below:

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	15
A	FG	15.01.2022	20	15
C	GG	15.05.2022	50	50
D	GT	16.07.2022	40	40

And it is working just fine, but I also want to have a slicer on Date which needs to have an impact on AvgNumber, so let's say I have a slicer with date > 15.01.2022

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	15
C	GG	15.05.2022	50	50
D	GT	16.07.2022	40	40

As you can see slicer removed row 2 from visual but AvgNumber measure calculated it anyway(and it make sense cause Date column was removed from filter context and it takes all Date fields it has in fact table for current product and type). So I tried to use AllSelected(date) as second filter of calculate but it didn't work. Using Calculate(Average(Number),AllSelected(table) shows on each line Avg of all Number in current filter context(when slicer is still > 15.01.2022)like below:

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	33,33
C	GG	15.05.2022	50	33,33
D	GT	16.07.2022	40	33,33

So basically what I want to achieve is create the AvgNumber measure which keeps visual context for two columns(Product, Type) except one(Date), but the one which is excluded(Date) should be measured by slicer.

Expected results with date slicer > 15.01.2022

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	10
C	GG	15.05.2022	50	50
D	GT	16.07.2022	40	40

Expected results with date slicer unrestricted

Product	Type	Date	Number	AvgNumber
A	FG	31.01.2022	10	15
A	FG	15.01.2022	20	15
C	GG	15.05.2022	50	50
D	GT	16.07.2022	40	40

It looks simple but I struggle...

Thanks in advance for any help!

[amitchandak · Accepted solution]

@Frajzerr , Try like

calculate(Average(Table[Number]), filter(allselected(Table), Table[Product]  = max(Table[Product]) && Table[Type]  = max(Table[Type])) )

[Frajzerr]

@amitchandak 

Thank you !!

It is working great, would you be so kind to give a short explanation of what your DAX query actually did ? 

I mean you propably used Filter to be able to use expressions in Calculate filter field right ? But I don't understand what field = max(field) is doing.