Forum Discussion
Dynamic ALLEXCEPT based on selected slicers.
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:
Any sugestion to convert this dax into a dynamic one.
Thank you so much!
Hi Anonymous
This should workQuantity of orders per Category = VAR SelectedDates = ALLSELECTED ( 'Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( ), VALUES ( 'Table'[Category] ), SelectedDates )
7 Replies
- littlemojopuppy
Community Champion
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?
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
- littlemojopuppy
Anonymous can you provide some sample data to work with?
- tamerj1
Hi Anonymous
This should workQuantity of orders per Category = VAR SelectedDates = ALLSELECTED ( 'Table'[Date] ) RETURN CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( ), VALUES ( 'Table'[Category] ), SelectedDates )Hi tamerj1 that's what I was looking for, thank you so much! 👍
- tamerj1
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]))
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
