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

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

     

    • DevDelwyn's avatar
      DevDelwyn
      Frequent Visitor
      7 years ago

      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")
      I have also attached a sample copy of the .pbix file
       
      As stated, the 'Latest Month Order Value 2' measure produces the correct total Month value, but this does not change when slicers select a different Cycle No. Band.