USERELATIONSHIP TWICE IN SAME MEASURE

[Anonymous]

Hi everyone!

I have a a mapping table for countries that connect to my main table in two relationships source and destination.

Im trying to calculate exports and imports with the following measure but it doesnt work

I get the following error

DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Any ideas?

Gas Pipe Exports = CALCULATE([Gas Outflow],'Nodes (Input)'[NodeType]="Transportation", 'New Mapping'[Country] <> CALCULATE(VALUES('New Mapping'[Country]), USERELATIONSHIP(LinkData[DestNodeName],'New Mapping'[Name])))

My connection is 

Country table

NodeName

Continent, 

Country

MainTable

SourceNodeName

DestNodeName

[v-alq-msft]

Hi, @Anonymous 

We recommend you pass filter arguments as Boolean expressions, whenever possible. It's because Import model tables are in-memory column stores. They are explicitly optimized to efficiently filter columns in this way.

However, there are some restrictions that apply to Boolean expressions when they're used as filter arguments. They:

• Cannot compare columns to other columns
• Cannot reference a measure
• Cannot use nested CALCULATE functions
• Cannot use functions that scan or return a table

Instead, you'll need to use table expressions for more complex filter requirements. For further information, I'd like to suggest you refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So Im getting closer to the solution but still not there

I created a measure to calculate the values Im trying to map first

1. Dest_Node_Type = CALCULATE(FIRSTNONBLANK('New Mapping'[Node Type],1),USERELATIONSHIP(LinkData[DestNodeName],'New Mapping'[Name]))

2. 

Dest_Country = CALCULATE(FIRSTNONBLANK('New Mapping'[Country],1),USERELATIONSHIP(LinkData[DestNodeName],'New Mapping'[Name]))

While Source Node Type  and source country I get it by just grabbing the field in table as it is the active relationship.

I then use it like this and seems to work as in I dont get any errors.

Gas Pipe Exports =

CALCULATE([Gas Outflow],'New Mapping'[Node Type]="Transportation", FILTER('New Mapping','New Mapping'[Country] <> [Dest_Country]))

However, when checking the mapping values in a table. The measures I'm using creating the use relationship measure seem to be picking the names from the active relationchip and not the unaactive relationship value.

Active Relationship:

Source Node name --> Name

Not active

Dest Node Name --> Name

Any ideas why this could be happening?

[Anonymous]

Hopefully this helps

I need to use a lot measures that require to filter based on dest and source names to get the country value in same measure

[az38]

Hi @Anonymous 

you can not use CALCULATE as a filter inside CALCULATE(). try to use var or filter() 

it's unclear for me what do you mean with this statement CALCULATE(VALUES('New Mapping'[Country])

[Anonymous]

Gas Pipe Exports = CALCULATE([Gas Outflow],'Nodes (Input)'[NodeType]="Transportation", FILTER('New Mapping','New Mapping'[Country]), USERELATIONSHIP(LinkData[SourceNodeName],'New Mapping'[Name]) <> FILTER('New Mapping','New Mapping'[Country]), USERELATIONSHIP(LinkData[DestNodeName],'New Mapping'[Name]))

This measure doesnt work either

Im trying to get the value of gas where the source country is not equal to the dest country.

the source and dest country values are in my mapping table

I need both values. and it says I can only use "userelationship" with calculate 😞

SourceNodename -- Name : Source Country Value

DestNodename --> Name: Dest Country Value

[Anonymous]

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

I GET THIS error using var