Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Wresen
Post Patron
Post Patron

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

 

 

Fowmy_0-1702895382687.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

8 REPLIES 8
cinnaban
Regular Visitor

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

Fowmy
Super User
Super User

@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]
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ahmedx
Super User
Super User

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

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

@Wresen 

Attach your PBIX file and explain the expected result.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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

 

 

Fowmy_0-1702895382687.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.