Is there any way where we can use SUMIFS in DAX without the relationship present.
I have two tables
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?
Solved! Go to Solution.
@smpa01 - Why doesn't the below query give me the total?
Is there any issue in the formula?
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.
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> ) ) )
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] ) ) )
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] )
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]))
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?
Hi @sid-poly ,
Here is new measure that uses the existing measue.
@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])
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
filter(Table2, Table2[Col] = max(Table1[Col]) && Table2[Col2] = max(Table1[Col2]) )
correction to column suggested
New column = sumx(filter(Table2, Table2[Col] = Table1[Col] && Table2[Col2] = Table1[Col2]), Table2[Value])
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.