Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is there a way to use ALLEXCEPT in filtering the input to a GROUPBY Table measure?
I am creating a dashboard to show comparitive figures between the latest report month and selected month that the user selects via slicer and can subsiquently be sliced by Cycle. The data shows Orders and Order Values but duplicates both of these fields as also contains Contacts for each order.
Order No | Order Value | Canc. Date Fix | Cycle No. Band | Contact Date | Contact Description |
708456 | 8.06 | 04/06/2019 | 1-3 | 01/06/2019 | AB1 |
708456 | 8.06 | 04/06/2019 | 1-3 | 03/06/2019 | AB2 |
849576 | 4.5 | 05/04/2019 | 4-7 | 20/03/2019 | AB5 |
519543 | 5.1 | 19/05/2019 | <1 | 19/05/2019 | AB1 |
8416791 | 8.1 | 20/05/2019 | 1-3 | 15/05/2019 | AB4 |
I have successfully created a measure to show the Distinct No. of Orders Cancelled in the latest month as follows.
Latest Month Cancellations = CALCULATE( DISTINCTCOUNT('All Clients'[Order No.]), filter( ALLEXCEPT( 'All Clients', 'All Clients'[Cycle No. Band], ), format(('All Clients'[Canc. Date Fix]),"MMM")=[Latest Report Month] ) )
However when applying this logic to a GroupBy so that the Order Value can also be sliced, the result is always the whole Month Value for latest month and will not pickup the cycle No. slicer. - It is as though it is treating the ALLEXCEPT as an ALL function. code attempt as follows
Latest Month Order Value Fix = var OrdValTBL2 = GROUPBY( FILTER( ALLEXCEPT( 'All Clients', 'All Clients'[Cycle No. Band], ), format('All Clients'[Canc. Date Fix],"MMM")=[Latest Report Month]) , 'All Clients'[Order No.], "Min Ord Val", minx(CURRENTGROUP(),[Order Value]) ) return sumx(OrdValTBL2,[Min Ord Val])
I don't know if I am asking the impossible or if my approach is incorrect but any assistance would be greatly received.
NB
The Groupby approach does produce correct figures without the Latest Month filter issue...
Order Value Fix = var OrdValTBL = GROUPBY( 'All Clients', 'All Clients'[Order No.], "Min Ord Val", minx(CURRENTGROUP(),[Order Value]) ) return sumx(OrdValTBL,[Min Ord Val])
If you're trying to show the data from previous month, try:
calculate ( [expression] , previousmonth ( [dates] ) )
Much much easier.
Hi DouweMeer,
That will not give me the correct results as the data contains duplicates i.e. in the example data above order 708456 is listed twice with its order value of 8.06 - so your suggestion would calculate its order value as 16.12.
The Groupby with ...
minx(CURRENTGROUP(),[Order Value])
..gives me the distinct order value for each order but I am not able to slice further in its current form.
If you're looking only for the distinct PO numbers as of previous month, you could do something like
VAR a1 = distinct ( selectcolumn ( 'table' , "column" , 'table'[column] )
to create your own table reference with distinct values. Then use that as a table to make your calculations on it.
RETURN
countx ( a1 , [column] )
There are multiple expressions that allow you to use a table reference instead of an actual table as context. You can even get string values from such a table if you ever need it.
this is a good alternative to the working measure above for distinct PO numbers but this is not the solution to the stated problem. Perhaps my original explenation was not clear?
I am trying to calculate the sum of order values for distinct order no.s for a filtered month.
The filtered month needs to remain regardless of slicer selections by users (as it is to compare against various months) so my understanding is that it must use some sort of ALL function otherwise the value will be affected by slicers.
How about use create a table with
Date - Value
Jan Stuff
Feb Stuff
Where Value equals:
Value =
VAR a1 = distinct ( selectcolumn ( 'table' , "PO" , 'table'[Order No] , "PO value" , 'table'[Order Value] )
RETURN
sumx ( a1 , [PO value] )
For previous month, just do:
calculate ( [Value] , previousmonth ( 'table'[Canc. Date Fix] )
Should do the trick I think.
Thank you DouweMeer for your patience & suggestions but I am confused by what you mean by 'create a table'? - Also I feel that it is not quite addressing my particular issue.
I am trying to create a measure that is fixed to the latest month so that the user can select other (various) months by slicer which will show variance against this measure, I would also like the slicer on other fields to pass through it.
My original formula is calculating the correct value for the latest month, but is not changing when other slicers are amended (i.e. [Cycle No. Band]).
What I would really like to understand is why the 'ALLEXCEPT' which states [Cycle No. Band] is acting soley as an 'ALL' function when filtering the input to the GROUPBY table? (ie it gives the whole month total regardless of which band is selected?).
Latest Month Order Value Fix = var OrdValTBL2 = GROUPBY( FILTER( ALLEXCEPT( 'All Clients', 'All Clients'[Cycle No. Band], ), format('All Clients'[Canc. Date Fix],"MMM")=[Latest Report Month]) , 'All Clients'[Order No.], "Min Ord Val", minx(CURRENTGROUP(),[Order Value]) ) return sumx(OrdValTBL2,[Min Ord Val])
Hi @DevDelwyn ,
What about changing ALLEXCEPT to ALLSELETED ?
In addition, if it is convenient, could you share the dummy pbix file or function of [Latest Report Month] so that I could have a test on it?
Best Regards,
Cherry
Hi @v-piga-msft ,
I cannot get ALLSELECTED to respond correctly, either it has no affect on the Latest Month Order Value Fix2 or responds with Multiple Arguments not allowed error. (...although I thought this function was for ignoring filters applied within the measure as opposed to from external slicers?)
[Latest Report Month] Measure is simply to get the name of the most recent month of Data.
Latest Report Month = format(CALCULATE(max('All Clients'[Canc. Date Fix]),all('All Clients')),"MMM")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |