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 th...
Need Help
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. 

DevDelwyn's avatar
DevDelwyn
Frequent Visitor
7 years ago

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.

 

 

 

 

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

    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.

    • DevDelwyn's avatar
      DevDelwyn
      Frequent Visitor
      7 years ago

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

       

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        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