SUMIFS in DAX without relationship

[sid-poly]

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

I have two tables

Table 1

Table 2

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?

[smpa01 · Accepted solution]

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

[sid-poly]

@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?

[sid-poly]

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.

[smpa01]

@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 

[Anonymous]

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

[smpa01]

@Anonymous yes sure can.

Here is an example with TREATAS

If you have the following

DAX can convert to this

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

[Anonymous]

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]

H @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

[sid-poly]

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.

Could you please help me down on the same?

[visheshjain]

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]

@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

[sid-poly]

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.

[amitchandak]

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