How to use aggregation on coalesced columns?

[ramasaurio]

I'm trying to use the function COALESCE to fill in missing values in a column.

Doing that was simple but when I try to use the values of that new measure, the aggregations show values as if it were the original column, so I must be doing something wrong.

I prepared this sample data (sample_data.pbix) which has these tables:

Table A:

Table B:

and a third table that I use to link both like this:

The measure I created is called "coalesce_measure":

and as you can see, the values for each day are correct but the total is still 11207 as if the value 9084 wasn't added. I tried using a card to show the sum of the column and it's the same value that appears in the matrix total.

What should I do to be able to use the correct sum? (14249)
What other considerations should I have while dealing with this function? (I'm assuming here that any other aggregation I would like to do will have to be dealt in a similar way as the sum)
Thanks a lot in advance.

[OwenAuger]

Glad to have helped 🙂

Sure, here's an explanation:

An important general point: Within a visual, the calculation of a measure at "total" level is independent of the calculation of the same measure at individual "row" levels.

The original measure was:

coalesce_measure =
COALESCE (
    SUM ( 'Table A'[value_A] ),
    SUM ( 'Table_B'[value_B] )
)

This measure 

1. Computes sum of value_A
2. Computes sum of value_B
3. Returns the first nonblank of these two values.

For individual dates, this gave you the expected result because the granularity of 'Table A' and 'Table B' happens to be date.

However, at a total level, both of the sums are nonblank because even though dates may be missing, at least one date exists in both 'Table A' and 'Table B'. So the sum of value_A is returned.

In other words, at the total level,

1. Sum of value_A = 11,207
2. Sum of value_B = 9,759
3. COALESCE ( 11,207, 9,759 ) = 11,207

The updated measure avoids this issue by iterating over a table of Date values from the dates table using SUMX.

For each date, the sums are calculated and coalesced, then the results for each date are summed.

coalesce_measure by date = 
SUMX (
    VALUES ( dates[date] ), -- dates would also work
    COALESCE( [Value A Sum], [Value B Sum] )
)

For an individual date, the results are the same as the original measure.

But for multiple dates the result is now correct because COALESCE is applied per date.

Here is a relevant article I would recommend reading to understand iterators and row context:

https://www.sqlbi.com/articles/row-context-in-dax/

Hope that helps! 🙂

Regards

[OwenAuger · Accepted solution]

Hi @ramasaurio 

If the logic is to coalesce at the date level, I would write measures as follows:

Value A Sum = 
SUM ( 'Table A'[value_A] )

Value B Sum = 
SUM ( Table_B[value_B] )

coalesce_measure by date = 
SUMX (
    VALUES ( dates[date] ), -- dates would also work
    COALESCE( [Value A Sum], [Value B Sum] )
)

Does this work for you?

[ramasaurio]

This worked perfectly.

I think I understand why this solution works, I'm making a table based on the values of column "date" of table dates, and then calculating the value of each row coalescing the measures of values A and B. But may I ask why my initial approach doesn't work? I'm fairly new to Power BI so I'm proabably missing some basic concept as to how measures work.

Thanks!