Compare two columns from different tables in Power BI

[db2907]

Hi there.

I've got this question. This is the model that has been created for the client (I can not modify anything only use it).

There is a many to many relationship between category and item. For this specific requirement it is necessary to create a metric that should have values only for a particular category, otherwise it should return 0 as value, However I can not show the ID of the category on the calculation. It's a specific requirement of the client. 

So I should have a metric with a logic similar to this:

CALCULATE( SUM(Fact[Amount]),

                     Category[Category ID] = Fact[Category ID])

On the report there will be a filter that will indicate the specific category for this metric. I only need to do this comparison.

Is there a way to do this on Power? So far I have not found any possible solution.

Regards

[amitchandak]

@db2907 , Ideally Category should be part of the Item table. But try one of the two as new measure

sumx(summarize(filter(Fact,Fact[Category ID] =max(Category[Category ID])),Fact[Category ID],"_1",sum(Fact[Amount])),[_1])

or

sumx(summarize(filter(Fact,Fact[Item ID] =max(Category[Item ID]) && Fact[Category ID] =max(Category[Category ID])),Fact[Category ID],"_1",sum(Fact[Amount])),[_1])

[mahoneypat]

Yes.  You should be able to use a measure like this

NewMeasures = var __thiscategory = SELECTEDVALUE(Category[CategoryID])

Return CALCULATE(Sum(Fact[Amount]), Fact[CategoryID]=__thiscategory)

This assumes there will only be a single value for Category[CategoryID] in the context you use it.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat