Explain the result from using measure in measure

[kaktuzzz]

Hi there,

My problem occurs in Excel 2016 rather than PowerBI, hope this makes no difference.

I have a dataset with 3 tables:

1. Positions table. For every branch of a company, specifies how much assets it has with every counterparty:

Branch	Counterparty Name	Position Size
US	ABC corp	10
GE	ABC corp	15
UK	ABC corp	23
FR	SUPERBEST INCORPORATED	8

2. RatingSummary table shows ratings of every counterparty with the date that rating has been granted:

Issuer Name	Rating	Rating Date
Superbest Inc.	A	18-3-2020
ABC corp	AA-	27-2-2020
Superbest Inc.	B	18-4-2020
ABC corp	AAA	27-1-2020

3. Providers of information in tables with Ratings and positions use different naming convention for counterparties. There's another table, CntptyNames, that is linked via [Issuer Name] to both tables above and maps naming conventions of both providers to a common naming convention, [Short Name]:

Issuer Name	Short Name
SUPERBEST INCORPORATED	Superbest
Superbest Inc.	Superbest
ABC corp	ABC
ABC corp.	ABC

The goal is: for every counterparty name my company has positions on, show the latest available rating and the date when this rating has been granted.

What I did:

1. Added a calculated column to Positions table: 

IssuerNameClean=RELATED(CntptyNames[Short Name]

2. Added a measure to positions table to return the latest date of rating update: 

LastUpdatedRating=MAXX(GENERATEALL(Positions,
SUMMARIZE(FILTER(ADDCOLUMNS(RatingSummary,"ShortName1",RELATED(CntptyNames[Short Name])), 
[ShortName1]=Positions[IssuerNameClean]),
[ShortName1],"LastRatingChange1",MAX(RatingSummary[Rating Date]))),
[LastRatingChange1] )

3. Added a Test measure to check the actual rating:

LatestRating=CALCULATE(FIRSTNONBLANK(RatingSummary[Rating],"NR"),FILTER(KEEPFILTERS(RatingSummary),RatingSummary[Rating Date]=[LastUpdatedRating]))

And the result of those surprised me:

Row Labels	LastUpdatedRating	LatestRating	Sum of Position Size
ABC	27-2-2020 9:40	AA-	48
Superbest	18-4-2020 22:27		8

While the LastUpdatedRating returns correct result, why is Latest rating only shown for ABC, but not for Superbest? And what should be the correct statement for this measure?

[technolog · Accepted solution]

Firstly, Excel's Power Pivot and Power BI both use the DAX language, so the principles are the same, but there might be some differences in the interface and features.

From your problem, it seems that the relationship between the tables and the DAX calculations are the main culprits.

The LastUpdatedRating measure you created seems to be working fine. It's fetching the latest rating date for each counterparty.

The issue seems to be with the LatestRating measure. The FIRSTNONBLANK function is used to return the first value that is not blank. However, in this context, it might not be the best choice. The reason being, if there are multiple ratings for a counterparty on the same date (which is the latest), FIRSTNONBLANK might not always return the expected result.

Instead, let's try a different approach for the LatestRating measure:

First, we need to ensure that the relationship between the tables is correctly set up. Make sure that there's a relationship between Positions[IssuerNameClean] and CntptyNames[Short Name] and between RatingSummary[Issuer Name] and CntptyNames[Issuer Name].

Now, for the LatestRating measure, we want to filter the RatingSummary table to only the rows where the Rating Date matches our LastUpdatedRating measure and then return the corresponding Rating.

Here's a possible DAX measure for this:

LatestRating =
VAR CurrentIssuer = MAX(Positions[IssuerNameClean])
VAR CurrentLastUpdatedRating = [LastUpdatedRating]
RETURN
CALCULATE(
MAX(RatingSummary[Rating]),
FILTER(
ALL(RatingSummary),
RELATED(CntptyNames[Short Name]) = CurrentIssuer && RatingSummary[Rating Date] = CurrentLastUpdatedRating
)
)
This measure first gets the current counterparty and the latest rating date. Then, it filters the RatingSummary table to only the rows that match these values and returns the Rating.

Try this out and see if it gives you the expected result for both ABC and Superbest.