Measures In Matrix Comprised of Multiple Datasets

[MLorenzen]

Hello all,

I've been searching for an answer to a matrix problem I've been having, but I can't find a similar situation to the one I'm in and I was hoping for some insight into the issue.

Quick disclaimer, I know that measures in matrices and using multiple datasets in one visual can both be tricky, so please feel free to critique my approach and offer better solutions in those regards.

The Problem:

Suppose I have three datasets, let's call them "Projects," "Assignments," and "Items." They each have a 1:* relationship between each other, in that order. So, one Project can have multiple Assignments, and one Assignment can have multiple Items.

I should mention, in case it's relevant, that for Assignments and Items I'm doing some DAX work with PATH functions to determine parent/child hieraches, as behind the scenes they both calculated from the same original dataset, but I separated them out with calculated tables. However, I've tested my cases described below with just two datasets not using any PATH work, and I still have issues.

My question comes from using measures that aggregate fields from the Items dataset in a matrix where the row headers are fields from Project -> Assignment -> Item (in that order).

Example:

Project (Row Header)	Assignment (Row Header)	Item (Row Header)	Calculated Sum (Value)	etc. (Values)
1	A	X	100	...
1	A	Y	150	...
1	B	Z	25	...
2	C	ZZZ	1000	...

When using certain measures for the values, I've noticed that the results become nonsensical, making it so that the matrix reports relationships that I can definitively say shouldn't exist. For instance, referring to the matrix above, I would see a row for Project 1 -> Assignment A -> Item Z, which doesn't make sense as Item Z isn't associated to that assignment. This continues for every Project in the matrix, having every Assignment listed under each Project, and every Item listed under each Assignment!

Here's an example of a measure that causes this scenario:

Measure = IF(SUM(Items[FieldA]) = 0, 0, SUM(Items[FieldB]) / SUM(Items[FieldA]))

However, rewriting that as the following causes the matrix to work as I'd imagine it should:

Measure = DIVIDE(SUM(Items[FieldB]), SUM(Items[FieldA]), 0)

I've also ran into that same crossing of data when doing something simple like the following measure:

Measure = "Literal text"

Does anyone have any insights into why this is happening? I wonder if maybe I'm unaware of some measure behavior or if the approach is flawed at the modeling level.

Thanks in advance for any help on this!

[MLorenzen]

@DataInsights, thanks for the reply and insight! DIVIDE definitely feels safer as the third parameter covers all bases.

However, why does that weird explosion of data happen when using a measure that just evaluates to literal text?

For instance, having the measure just return "Text" caused the issue I was describing. Why would that be, and is there any way to avoid that?

I ask because I'm trying to do something clever for implementing a URL link in the matrix's values (which I know is tricky to do) but having the text not appear in any totals. I can describe this more if necessary, but regardless the matrix behavior with the simple measure is confusing to me.

[DataInsights]

@MLorenzen,

I believe the explosion of data is due to the data model. Notice what happens when I use Items[Assignment ID] in the matrix using your original measure:

Nonexistent combinations such as 1-A-ZZZ disappear as a result of not using the intermediate table Assignments. Power BI works best with a star schema (fact and dimension tables). There are a lot of great articles on this topic.

Regarding the URL, you can use HASONEVALUE to determine if a row is a total row (and thus not display the URL).

[DataInsights]

@MLorenzen,

This behavior is due to how the engine handles 0 and BLANK. See the article below for a detailed explanation:

https://dax.guide/op/strictly-equal-to/

Here's an illustration using your data model. I recommend using the third measure (DIVIDE), since it avoids nonexistent combinations and handles division by 0.

Data:

Measures:

Measure (IF equal) = 
IF (
    SUM ( Items[FieldA] ) = 0,
    0,
    SUM ( Items[FieldB] ) / SUM ( Items[FieldA] )
)

Measure (IF strictly equal) = 
IF (
    SUM ( Items[FieldA] ) == 0,
    0,
    SUM ( Items[FieldB] ) / SUM ( Items[FieldA] )
)

Measure (DIVIDE) = DIVIDE ( SUM ( Items[FieldB] ), SUM ( Items[FieldA] ), 0 )

Result: