Forum Discussion

Wresen's avatar
Wresen
Icon for Post Patron rankPost Patron
2 years ago
Solved

SUMMARIZE does not use the USERELATIONSHIP

Hi

Thanks for reading this.

I have a USERELATIONSHIP between two tables , the "normal" measure with USERELATIONSHIP work as it should and it looks like this:
CALCULATE(sum(Table[Total]),Table[Month] = "202306" , Table[Salemonth] >= "202306",USERELATIONSHIP(Table[SaleMonth],Monthtable[Year-Month]))

 
 
 
But when i put this in SUMMARIZE it ignores the USERELATIONSHIP.
I wounder if anyone see any completly woring in the measure.

Calc on Item = sumx(ADDCOLUMNS(
        SUMMARIZE( Table' ,'Table'[Month] , 'Table'[Item] ),"Calc" ,
     CALCULATE(sum(Table[Total]),Table[Month] = "202306" , Table[Salemonth] >= "202306",USERELATIONSHIP(Table[SaleMonth],Monthtable[Year-Month]))
    ),
    [Calc]
)
 
Thanks so much for any help
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    Wresen 

    I modified your measure and now the result should be as expected. However, I am not sure the context for this meaure in your real application, please make necessary changes.

     

    Not working Sales Proc Calc = 
CALCULATE(
    sumx(
        ADDCOLUMNS(
            SUMMARIZE( 'Table' ,'Month'[Year-Month] , 'Table'[Item] ),
            "Calc" , 
            CALCULATE(
                sum('Table'[Total]),
                'Table'[Month] = "202301" , 
                'Table'[SalesMonth] >= "202301"
                
            )
        ),
        [Calc]
    ),
    USERELATIONSHIP('Table'[SalesMonth],'Month'[Year-Month])
)

     

     

     

8 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

    Calc on Item = sumx(
ADDCOLUMNS(
        SUMMARIZE( Table' ,'Table'[Month] , 'Table'[Item] ),
    "Calc" ,
        CALCULATE(sum(Table[Total]),Table[Month] = "202306" , Table[Salemonth] >= "202306",
          TREATAS(VALUES(Table[SaleMonth]),VALUES(Monthtable[Year-Month])))
    ),
    [Calc]
)

    if this doesn't help please share the file to help you

    • Wresen's avatar
      Wresen
      Icon for Post Patron rankPost Patron
      2 years ago

      Thx Fowmy  and Ahmedx 

      i did not get your codes to work , i am working on a small example of the code (since its from a big data)
      I have done some more checking and see that it filters correct but the count/sum value seems to be wrong (it counts/sums wrong but the data is there)
      /thanks

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    Wresen 

    Try below measure:

    Calc on Item =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Month], 'Table'[Item] ),
        "Calc",
            CALCULATE (
                SUM ( Table[Total] ),
                Table[Month] = "202306",
                Monthtable[Year-Month] >= "202306",
                USERELATIONSHIP ( Table[SaleMonth], Monthtable[Year-Month] )
            )
    ),
    [Calc]
)
  • cinnaban's avatar
    cinnaban
    Regular Visitor
    2 years ago

    I had this exact same problem, and your solution worked perfectly thank you!