- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Is there a way I can add 1 more criterion in this DAX expression?
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 07:04 AM - edited 01-11-2019 09:08 AM
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!
Solved! Go to Solution.
Accepted Solutions
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-13-2019 03:27 AM
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?
All Replies
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 07:20 AM
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], ", " )
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 09:20 AM
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?
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 10:10 AM
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], ", " )
Re: Is there a way I can add 1 more criterion in this DAX expression?
[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 10:43 AM - edited 01-11-2019 11:07 AM
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!
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 11:17 AM
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], ", " )
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-11-2019 11:27 AM
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!
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-13-2019 03:27 AM
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?
Re: Is there a way I can add 1 more criterion in this DAX expression?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
01-13-2019 03:33 AM
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 )