cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sid-poly
Frequent Visitor

SUMIFS in DAX without relationship

Is there any way where we can use SUMIFS in DAX without the relationship present.

 

I have two tables

 

Table 1

sidpoly_1-1641451963290.png

 

 

 

Table 2

sidpoly_0-1641451925004.png

 

I know for a fact that most of them would ask me to relate the table above as there is one to many relationship. If there are multiple columns and I have to do validation based on criterias then I would use SUMIFS. Can anyone help me down with the expression for bringing values from table 2 to table 1?

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@sid-poly  there are at least 4 different ways to get what you want without relationship. Please refer to the attached pbix.

 

 

smpa01_0-1641480794831.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

13 REPLIES 13
smpa01
Super User
Super User

@sid-poly  there are at least 4 different ways to get what you want without relationship. Please refer to the attached pbix.

 

 

smpa01_0-1641480794831.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@smpa01 - Why doesn't the below query give me the total?

CALCULATE(SUM(Table2[Records]),FILTER(Table2,Table2[Col_one]=IF(HASONEVALUE(Table1[col_one]),VALUES(Table1[col_one])))

 

Is there any issue in the formula?

Hi @smpa01 

 

Can you just explain me how contains work? As I see that there is no filter appied, I am quite trying to understand the execution of it.

 

@sid-poly 

 

contains = 
CALCULATE (
    SUM ( Table2[records] ),
    FILTER (
        Table2,
        CONTAINS ( VALUES ( Table1[col_one] ), Table1[col_one], Table2[col_one] )
    )
)


contains=
CALCULATE (
    <target_measure>,
    FILTER (
         <target_tbl> ,
        CONTAINS (
            VALUES ( <lookup_granularity_column> ),
            <lookup_granularity_column>,
            <target_granularity_column>
        )
    )
)

 

recommended reading 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Hey @smpa01, thanks for your clear response. I'm trying to expand this to match multiple fields. Would the following work for matching two (or more) columns instead of one? It gets me close, but I'm in over my head.

contains = 
CALCULATE (
    SUM ( Table2[records] ),
    FILTER (
        Table2,
        CONTAINS ( SUMMARIZE ( Table1, Table1[col_one], Table2[col_two] ), Table1[col_one], Table2[col_one], Table1[col_two], Table2[col_two] )
    )
)

 

@PBI_is_poo yes sure can.

Here is an example with TREATAS

If you have the following

 

smpa01_0-1661433996164.png

 

DAX can convert to this

 

smpa01_1-1661434096336.png

 

 

Measure =
CALCULATE (
    SUM ( t2[Val] ),
    TREATAS ( SUMMARIZE ( t1, t1[col_one], t1[col_two] ), t2[col_one], t2[col_two] )
)

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Thanks, this helped too. But, what I realised (and my warning for anyone stumbling accross this post), is that setting up relationships is SO much better. The limitation without relationships is that we cannot filter by any fields not contained in the SUMMARIZE function. Setting up a table with unique values of both/all tables, relating it and using that in the slicer/filter allows the original measure above to become:

Measure = SUM ( t2[Val] )

 

visheshjain
Super User
Super User

@sid-poly ,

 

Here are 2 solutions, 1 is a measure and the other is a calculated column.

I have named the tables Dim and Data.

 

Total Measure =
var selected_name = SELECTEDVALUE(Dim[Names])
var Result = Calculate(SUM(Data[Value]), Data[Names] = selected_name)
Return
Result

Total Column = Calculate(SUM(Data[Value]), Filter(Data,Data[Names] = Dim[Names]))
 
Hope this helps.
 
Thank you,
 
Vishesh Jain

Hi Vishesh,

Thanks for the help. It did work, but I am facing an issue with the same. I see that it doesn't give me the total value even if it is enabled.

sidpoly_0-1641456812765.png

 

Could you please help me down on the same?

Hi @sid-poly ,

 

Here is new measure that uses the existing measue.

 

Final Total = IF(
ISINSCOPE(Dim[Names]), [Total],
SUMX(VALUES(Dim[Names]), [Total])
)
 
I am unable to figure out a way to do this is the same measure, but this should work.
 
Hope this helps.
 
Thank you,
 
Vishesh Jain
amitchandak
Super User
Super User

@sid-poly , You can move across the table . Example change as per need

 

A new column in table 1

New column = sumx(Table2, Table2[Col] = Table1[Col] && Table2[Col2] = Table1[Col2]) Table2[Value])

 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Is there any way round I can use a measure and get it? As I see if we use the column I have many values in my table 2 and it will duplicate. So that It can help me down.

@sid-poly , Merge in power query with concatenated keys could be option for more than one column

 

Now for measure, you need a context

measure =
calculate(sumX(values(Table2[ID]), Table2[Value]),

filter(Table2, Table2[Col] = max(Table1[Col]) && Table2[Col2] = max(Table1[Col2]) )

 

or refer https://docs.microsoft.com/en-us/dax/treatas-function

 

correction to column suggested

New column = sumx(filter(Table2, Table2[Col] = Table1[Col] && Table2[Col2] = Table1[Col2]), Table2[Value])



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors