Need help with an equivalent field measure

[Anonymous]

Hi all,

I've been having some issues with a measure and would love some assistance!

I have a table with a list of projects and their correlating spend:

Project ID	Spend
183	$1,000.00
184	$2,000.00

However some projects are yet to record any spend and therefore have been asigned an equivalent ID:

Project ID	Equivalent Project ID
183
500	184

These tables are linked by the project ID field.

I need a measure that Sums the spend for the equivalent project ID.

So in the example above, I would select Project 500 and have a measure that outputs 2,000 (the spend from the equivalent ID).

Appreciate all suggestions.

Thanks!

[amitchandak]

@Anonymous , see if treat as can help you

https://docs.microsoft.com/en-us/dax/treatas-function

Or measure like

meausre =

var _tab= distinct(filter(Table2,not(isblank(Table2[Equivalent Project ID]))),Table2[Equivalent Project ID])
return
calculate(sum(Table[Spend]),filter(Table1, table1[Project ID] in _tab))

[Anonymous]

Hi @amitchandak,

Thanks for your suggestions, I really appreciate your efforts to solve my issue.

Unfortunately both options have returned blanks (eg. not pulling through the spend correctly).

I'll keep searching for a solution.

[amitchandak]

@Anonymous , check if this can work on bigger data. The file is attached after the signature .

You have to use project id from table 2, in spite of having common project table

[Anonymous]

Hi @amitchandak,

I think you are very close to solving that issue for me.

Your measure is doing what I want it to do, however, I need to be able to drive the project ID off the bridging table you have created (as this is the field I'm driving all of my measures from). 

In your example, it is being driven by the project fact table rather than the bridging table.

Do you think it's possible to drive it from the bridging table?

Thanks,

[amitchandak]

@Anonymous , I need check back, I wanted that, but as both table are connected , and data is comming table which joined and filter is needed from another joined table. This is bit of trick. 

Can you share a sample where this solution is failing. May be that can help 

[Anonymous]

Hi @amitchandak,

Below is an example

I want to show that Project ID '500' has $2,000 spend when taking into account the equivalent project ID.

Your example works perfectly when you drive the visual off the project ID from table 2 (see screenshot below).

However when you drive the visual from the project ID in the bridging table, the visual returns the equivalent ID (#184), not the actual project ID #500 (see screenshot below).

My bridging table is linked to multiple different fact tables (all being used in the one visual) and therefore I need to drive all visuals off the bridging table.

Thanks!