topic Re: SUMIFS in DAX without relationship in DAX Commands and Tips

SUMIFS in DAX without relationship

Anonymous — Thu, 06 Jan 2022 07:14:35 GMT

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?

Re: SUMIFS in DAX without relationship

amitchandak — Thu, 06 Jan 2022 07:16:38 GMT

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

Re: SUMIFS in DAX without relationship

Anonymous — Thu, 06 Jan 2022 07:22:40 GMT

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.

Re: SUMIFS in DAX without relationship

visheshjain — Thu, 06 Jan 2022 07:56:18 GMT

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

Re: SUMIFS in DAX without relationship

amitchandak — Thu, 06 Jan 2022 07:56:31 GMT

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

Re: SUMIFS in DAX without relationship

Anonymous — Thu, 06 Jan 2022 08:13:53 GMT

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?

Re: SUMIFS in DAX without relationship

visheshjain — Thu, 06 Jan 2022 08:36:02 GMT

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

Re: SUMIFS in DAX without relationship

smpa01 — Thu, 06 Jan 2022 14:53:23 GMT

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

Re: SUMIFS in DAX without relationship

Anonymous — Thu, 06 Jan 2022 16:07:31 GMT

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.

Re: SUMIFS in DAX without relationship

smpa01 — Thu, 06 Jan 2022 16:23:09 GMT

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

Re: SUMIFS in DAX without relationship

Anonymous — Sat, 08 Jan 2022 08:01:47 GMT

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?

Re: SUMIFS in DAX without relationship

Anonymous — Wed, 24 Aug 2022 06:01:28 GMT

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

Re: SUMIFS in DAX without relationship

smpa01 — Thu, 25 Aug 2022 13:30:35 GMT

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

Re: SUMIFS in DAX without relationship

Anonymous — Tue, 18 Oct 2022 05:07:37 GMT

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