Forum Discussion

DevDelwyn's avatar
DevDelwyn
Frequent Visitor
7 years ago

GROUPBY with ALLEXCEPT filter in measure

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 NoOrder ValueCanc. Date FixCycle No. BandContact DateContact Description
7084568.0604/06/20191-301/06/2019AB1
7084568.0604/06/20191-303/06/2019AB2
8495764.505/04/20194-720/03/2019AB5
5195435.119/05/2019<119/05/2019AB1
84167918.120/05/20191-315/05/2019AB4

 

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])

 

 

 

Need Help

8 Replies

  • DouweMeer's avatar
    DouweMeer
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    If you're trying to show the data from previous month, try:

    calculate ( [expression] , previousmonth ( [dates] ) )

    Much much easier.

    • DevDelwyn's avatar
      DevDelwyn
      Frequent Visitor
      7 years ago

      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.

      • DouweMeer's avatar
        DouweMeer
        Icon for Impactful Individual rankImpactful Individual
        7 years ago

        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.