SUMMARIZE does not use the USERELATIONSHIP

[Wresen]

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

[cinnaban]

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

[Fowmy]

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

[Ahmedx]

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]

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]

@Wresen 

Attach your PBIX file and explain the expected result.

[Wresen]

Hi

Attached a file:
The Measure "Not Working Sales Proc Calc" should behave as Salescount.
(i need it to have in the format i have it now with Summarize since this is only a little part of the code and calculation you see)
/Thanks for any help to figure this out 🙂




https://drive.google.com/file/d/1jqdQXOM4sgKY96RxVQoxf8QKndx9A5jX/view?usp=sharing

[Fowmy · Accepted solution]

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

[Wresen]

Thanks so much for the help @Fowmy  , i really appriciate it 🙂