cancel
Showing results for
Did you mean:
Regular Visitor

## Is there a way I can add 1 more criterion in this DAX expression?

I have the following DAX expression:

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
'Full Data'[Engine Model] IN mymodel
),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

I don't have any programming knowledge but I'm aware that this Others measure was written to response only to the "Model" slicer.

Is there a way to make this measure response both to the 'Full Data'[Engine Model] & 'Full Data'[Material Group] with the rest of the code staying the same?

Thank you so much!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

@iamtrangdoan

I made a mistake and updated the older version that threw the error instead of the latest one. Try this:

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
VAR mygroup =
VALUES ( 'Compare Data'[Group] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
FILTER('Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup
)),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

I am confused with the slicer story though. It would make more sense if what you select in the slicer is 'Compare Data'[Name] rather than 'Compare Data'[Engine Moldel] or 'Compare Data'[Group]  but maybe I'm missing something. Is what you show from the 'Compare Data' table the full table or only a fragment?

8 REPLIES 8
Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

Although it is not completely clear what you need, try this. Check out the bit in red. I guess you need to update that with the correct column.

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
VAR mygroup =
VALUES ( 'Compare Data'[Group] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
'Full Data'[Engine Model] IN mymodel && 'Full Data'[Engine Model] IN mygroup
),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

Regular Visitor

## Re: Is there a way I can add 1 more criterion in this DAX expression?

Hi @AlB, thanks for your help!

I tried yours with my real data, got this message with the measure: "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression".

Any ideas?

Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

@iamtrangdoan

Try this to eliminate that error message. But we still have the issue with the bit in red. From the info provided, I am not sure what you need there.

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
VAR mygroup =
VALUES ( 'Compare Data'[Group] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
FILTER('Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Engine Model] IN mygroup
)),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

Regular Visitor

## Re: Is there a way I can add 1 more criterion in this DAX expression?

Hi @AlB,

Just like in the original post, I know the code creates a measure that responds to the 'Full Data'[Engine Model] . I need it to respond both to 'Full Data'[Engine Model] & 'Full Data'[Material Group].

I tried the one you modified, gave me blanks though.

I don't know if it helps but here is my data:

1. "Compare Data" table:

 Name Model Material No Group Cost A W5 1005-01 EEC 100 A W5 1005-02 EEC 150 A W3 1005-03 DIVIDER 105 A W3 1005-04 DIVIDER 3000 A W5 1005-05 EEC 1215

2. "Full Data" table

 Vendor Name Engine Model Material Number Material Group Repair Cost A W5 1005-01 EEC 80 B W5 1005-02 EEC 85 C W3 1005-03 DIVIDER 135 D W3 1005-04 DIVIDER 3005 E W5 1005-05 EEC 1115 F W2 1005-08 EEC 250 A W5 1005-09 EEC 350 A W5 1005-03 EEC 135 A W5 1005-25 DIVIDER 125 B W3 1005-2 DIVIDER 125.5 B W15 1005-3 EEC 135 R W5 1005-2 EEC 450

Thank youuu!

Highlighted
Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

@iamtrangdoan

Try this then, where we've changed the bit in blue

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
VAR mygroup =
VALUES ( 'Compare Data'[Group] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup
),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

Regular Visitor

## Re: Is there a way I can add 1 more criterion in this DAX expression?

Hi @AlB. I tried it in the first place when you said to change the one in red. Return the same message as I did before.

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Basically my aim for this measure is to find any alternative suppliers to A (A's from the "Compare Data" table) who are capable of repairing certain models or share the same material groups with A. That's why I said I wanted the measure to respond to the 'Full Data'[Engine Model] & the 'Full Data'[Material Group]

With the DAX expression in my original post, I wasn't able to get the measure to work when selections were made on both slicers. The results were always based on the Engine Model slicer but noth both even though both slicer selections were chosen.

Please tell me if you find anything unclear. Thank you for your hekp so far!

Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

@iamtrangdoan

I made a mistake and updated the older version that threw the error instead of the latest one. Try this:

```Others =
VAR mymodel =
VALUES ( 'Compare Data'[Model] )
VAR mygroup =
VALUES ( 'Compare Data'[Group] )
RETURN
CONCATENATEX (
EXCEPT (
CALCULATETABLE (
VALUES ( 'Full Data'[Vendor Name] ),
FILTER('Full Data', 'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup
)),
VALUES ( 'Compare Data'[Name] )
),
[Vendor Name],
", "
)```

I am confused with the slicer story though. It would make more sense if what you select in the slicer is 'Compare Data'[Name] rather than 'Compare Data'[Engine Moldel] or 'Compare Data'[Group]  but maybe I'm missing something. Is what you show from the 'Compare Data' table the full table or only a fragment?

Super User

## Re: Is there a way I can add 1 more criterion in this DAX expression?

@iamtrangdoan

Additionally, I still don't quite understand when you want to show a supplier since you talk about model OR material and then model AND material. It would help if you provide a couple of examples on the sample data to show when the supplier would be valid and when not.  I suspect that, based on that clarification, we will have to update the condition in the FILTER( ) statement:

```FILTER('Full Data',       'Full Data'[Engine Model] IN mymodel && 'Full Data'[Material Group] IN mygroup
)```