topic Re: Dynamic ALLEXCEPT based on selected slicers. in DAX Commands and Tips

Dynamic ALLEXCEPT based on selected slicers.

Anonymous — Fri, 04 Mar 2022 17:23:59 GMT

I have a measure with ALLEXCEPT DAX command for count the quantity of orders per equipment category. But it is static, but in order to have a real count depending of the selection in the slicers in need to transform that measure into a dynamic count.

For example:

Category: Trucks	Quantity of orders per Category: 10
Truck 1	10
Truck 2	10
Truck 3	10
Truck 4	10
Category: Mixers	Quantity of orders per Category: 15
Mixer 1	15
Mixer 2	15

But in this example if the user select another date or another country, the table is going to have the same values because i'm using the following DAX command:

Quantity of orders per Category =

CALCULATE(COUNT(Table[Order]),ALLEXCEPT(Table,Table[Category]))

Any sugestion to convert this dax into a dynamic one.

Thank you so much!

Re: Dynamic ALLEXCEPT based on selected slicers.

littlemojopuppy — Fri, 04 Mar 2022 19:05:55 GMT

Hi Anonymous the reason it's not changing is because ALLEXCEPT() only includes Category as a field to ignore filters.

But I'm thinking you might be overcomplicating this...have you tried just simply COUNT(Table[Order]) as your measure? That would take into account filter context for Category, Date, Country, or whatever other filters are applied. If you did, what happened?

Re: Dynamic ALLEXCEPT based on selected slicers.

Anonymous — Fri, 04 Mar 2022 21:20:33 GMT

Thanks for your answer littlemojopuppy
If I just make a simply COUNT(Table[Order]), the measure is going to count the amount of orders by equipment but not by category. In the example the table is going to be something like:

Category: Trucks	Quantity of orders per Category: 10
Truck 1	4
Truck 2	2
Truck 3	1
Truck 4	3
Category: Mixers	Quantity of orders per Category: 15
Mixer 1	9
Mixer 2	6

Thanks

Re: Dynamic ALLEXCEPT based on selected slicers.

littlemojopuppy — Fri, 04 Mar 2022 21:37:03 GMT

Anonymous can you provide some sample data to work with?

Re: Dynamic ALLEXCEPT based on selected slicers.

tamerj1 — Mon, 07 Mar 2022 18:51:27 GMT

Hi Anonymous

You can add the date column and the country column inside ALLEXCEPT

Alternatively, you can use

CALCULATE(COUNT(Table[Order]),REMOVEFILTERS(Table[Equipment]))

Re: Dynamic ALLEXCEPT based on selected slicers.

Anonymous — Tue, 08 Mar 2022 16:57:14 GMT

Hi littlemojopuppy here is some sample data, as you can see the last column have the total quantity of orders by category, but in the second table if I select a different date, the results must change.

Country	Date	Order	Equipment	Category	Q orders per Category
US	01-02-22	4868	Kiln2	Kilns	18
Colombia	02-02-22	4906	Kiln2	Kilns	18
Colombia	03-02-22	4598	Kiln3	Kilns	18
Brazil	04-02-22	4532	Mixer 1	Mixers	9
Colombia	05-02-22	4626	Truck 3	Trucks	21
Colombia	06-02-22	4797	Mixer 1	Mixers	9
US	07-02-22	4817	Kiln3	Kilns	18
US	08-02-22	4557	Truck 4	Trucks	21
Canada	09-02-22	4923	Truck 3	Trucks	21
Brazil	10-02-22	4773	Truck 3	Trucks	21
US	11-02-22	4807	Truck 2	Trucks	21
Mexico	12-02-22	4654	Kiln1	Kilns	18
Colombia	13-02-22	4661	Kiln3	Kilns	18
Canada	14-02-22	4987	Mixer 1	Mixers	9
Colombia	15-02-22	4965	Truck 4	Trucks	21
US	16-02-22	4737	Truck 1	Trucks	21
Colombia	17-02-22	4938	Truck 1	Trucks	21
US	18-02-22	4938	Truck 1	Trucks	21
Brazil	19-02-22	4821	Truck 2	Trucks	21
Mexico	20-02-22	4966	Truck 1	Trucks	21
Brazil	21-02-22	4752	Truck 4	Trucks	21
Mexico	22-02-22	4827	Kiln3	Kilns	18
US	23-02-22	4931	Truck 4	Trucks	21
Mexico	24-02-22	4677	Kiln1	Kilns	18
US	25-02-22	4768	Truck 3	Trucks	21
Colombia	26-02-22	4640	Truck 1	Trucks	21
Brazil	27-02-22	4747	Truck 4	Trucks	21
Colombia	28-02-22	4638	Mixer 1	Mixers	9
Canada	01-03-22	4928	Kiln2	Kilns	18
Mexico	02-03-22	4583	Kiln1	Kilns	18
Brazil	03-03-22	4935	Truck 2	Trucks	21
Canada	04-03-22	4848	Kiln1	Kilns	18
Colombia	05-03-22	4566	Mixer 1	Mixers	9
Mexico	06-03-22	4802	Truck 2	Trucks	21
Colombia	07-03-22	4640	Mixer 2	Mixers	9
Canada	08-03-22	4603	Truck 3	Trucks	21
Canada	09-03-22	4786	Kiln2	Kilns	18
Canada	10-03-22	4752	Mixer 1	Mixers	9
Mexico	11-03-22	4724	Mixer 2	Mixers	9
US	12-03-22	4738	Kiln3	Kilns	18
Brazil	13-03-22	4961	Kiln1	Kilns	18
Mexico	14-03-22	4688	Kiln2	Kilns	18
Canada	15-03-22	4798	Mixer 1	Mixers	9
Colombia	16-03-22	4786	Kiln1	Kilns	18
Colombia	17-03-22	4586	Truck 1	Trucks	21
Brazil	18-03-22	4643	Kiln2	Kilns	18
Mexico	19-03-22	4614	Kiln1	Kilns	18
Colombia	20-03-22	4743	Truck 4	Trucks	21

Filtered only by dates in March

Country	Date	Order	Equipment	Category	Q orders per Category
Canada	01-03-22	4928	Kiln2	Kilns	10
Mexico	02-03-22	4583	Kiln1	Kilns	10
Brazil	03-03-22	4935	Truck 2	Trucks	5
Canada	04-03-22	4848	Kiln1	Kilns	10
Colombia	05-03-22	4566	Mixer 1	Mixers	5
Mexico	06-03-22	4802	Truck 2	Trucks	5
Colombia	07-03-22	4640	Mixer 2	Mixers	5
Canada	08-03-22	4603	Truck 3	Trucks	5
Canada	09-03-22	4786	Kiln2	Kilns	10
Canada	10-03-22	4752	Mixer 1	Mixers	5
Mexico	11-03-22	4724	Mixer 2	Mixers	5
US	12-03-22	4738	Kiln3	Kilns	10
Brazil	13-03-22	4961	Kiln1	Kilns	10
Mexico	14-03-22	4688	Kiln2	Kilns	10
Canada	15-03-22	4798	Mixer 1	Mixers	5
Colombia	16-03-22	4786	Kiln1	Kilns	10
Colombia	17-03-22	4586	Truck 1	Trucks	5
Brazil	18-03-22	4643	Kiln2	Kilns	10
Mexico	19-03-22	4614	Kiln1	Kilns	10
Colombia	20-03-22	4743	Truck 4	Trucks	5

Re: Dynamic ALLEXCEPT based on selected slicers.

tamerj1 — Tue, 08 Mar 2022 18:00:06 GMT

Hi Anonymous
This should work

Quantity of orders per Category = VAR SelectedDates = ALLSELECTED ( 'Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( ), VALUES ( 'Table'[Category] ), SelectedDates )

Re: Dynamic ALLEXCEPT based on selected slicers.

Anonymous — Wed, 09 Mar 2022 17:25:11 GMT

Hi tamerj1 that's what I was looking for, thank you so much! 👍