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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.