Forum Discussion
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
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?
Anonymous there are at least 4 different ways to get what you want without relationship. Please refer to the attached pbix.
13 Replies
- visheshjain
Impactful Individual
H Anonymous ,
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- visheshjain
Hi Anonymous ,
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
Anonymous , 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- amitchandak
Anonymous , 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])
smpa01Community Champion
Anonymous there are at least 4 different ways to get what you want without relationship. Please refer to the attached pbix.
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
Anonymous
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> ) ) )
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?
