topic Re: Get value of partner ticker in DAX Commands and Tips

Get value of partner ticker

oanu — Mon, 27 Nov 2023 10:57:26 GMT

Hi all,

I would like to get the latest value of “Counterpart” ticker (aka “Peer”) of the selected ticker. Below are the details:

Model has 4 tables: Dim_Assets contains some metadata for tickers; Peers table specify which Ticker has which Peer (Peer is also Ticker Code from Dim_Assets); Historical Data is obvious.

All Ticker Codes have historical data. Several Ticker code could have the same Peer.

I have some measures which work just fine:

Current Value = CALCULATE(SUM('Fact_Historical_Data'[Value]),LASTDATE('Fact_Historical_Data'[Period])) Date of Current Value = CALCULATE(SUM('Fact_Historical_Data'[Period]),LASTDATE('Fact_Historical_Data'[Period])) Peer Existence Check = VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code]) RETURN LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, Blank()) Peer Latest Period = VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code]) VAR PeerTicker = LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, BLANK()) RETURN IF(NOT ISBLANK(PeerTicker), CALCULATE(MAX(Fact_Historical_Data[Period]), Fact_Historical_Data[Ticker Code] = PeerTicker, ALL(Fact_Historical_Data)), BLANK() )

However, the measure to get the value of Peer always returns blank:

Peer Current Value = VAR LatestPeriod = CALCULATE(MAX(Fact_Historical_Data[Period]), ALL(Fact_Historical_Data)) VAR CurrentTicker = SELECTEDVALUE(Dim_Assets[Ticker Code]) VAR PeerTicker = LOOKUPVALUE(Peers[Peer Code], Peers[Ticker Code], CurrentTicker, BLANK()) RETURN IF(ISBLANK(PeerTicker), BLANK(), CALCULATE( SUM(Fact_Historical_Data[Value]), Fact_Historical_Data[Ticker Code] = PeerTicker, Fact_Historical_Data[Period] = LatestPeriod ) )

Could you pls give a hint here.

regards,

Alex

Re: Get value of partner ticker

WinterMist — Mon, 27 Nov 2023 13:52:41 GMT

oanu

Is there any chance you can upload the PBIX to Google Drive and share the link in this thread?

Regards,

Nathan

P.S. If not, would it be possible to create a PBIX with a small amount of mock data that should work?

Re: Get value of partner ticker

WinterMist — Mon, 27 Nov 2023 20:36:24 GMT

oanu

I'm wondering if this has to do with the data.

What happens if you remove the Alternate_Result parameter from VAR PeerTicker and simply return the VAR PeerTicker?

Your ALTERNATE RESULT = BLANK().

If it throws an error, that would tell me that more than one value is getting returned.

(And if you're getting more than one value, that makes sense why the entire measure is returning BLANK all the time.)

Regards,

Nathan

Re: Get value of partner ticker

oanu — Tue, 28 Nov 2023 13:15:41 GMT

Hi all,

Thank you for support. I decided to move column "Peer Code" to Dim_Assets Table and also adjusted the "Peer Current Value" measure as follows:

Peer Current Value = VAR LatestPeriod = LASTDATE('Fact_Historical_Data'[Period]) VAR PeerTicker = SELECTEDVALUE(Dim_Assets[Peer Code]) RETURN IF( ISBLANK(PeerTicker), BLANK(), CALCULATE( SUM(Fact_Historical_Data[Value]), All(Fact_Historical_Data), Fact_Historical_Data[Ticker Code] = PeerTicker, Fact_Historical_Data[Period] = LatestPeriod ) )

So I added filter "All(Fact_Historical_Data)" and now it works.

regards,

Alex