Reply
Regular Visitor
Posts: 37
Registered: ‎09-20-2018
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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? 

 

View solution in original post


All Replies
Highlighted
AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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

Hi  @iamtrangdoan

 

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
Posts: 37
Registered: ‎09-20-2018

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?

 

 

AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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
Posts: 37
Registered: ‎09-20-2018

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

[ Edited ]

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:

NameModelMaterial NoGroupCost
AW51005-01EEC100
AW51005-02EEC150
AW31005-03DIVIDER105
AW31005-04DIVIDER3000
AW51005-05EEC1215

 

2. "Full Data" table

Vendor NameEngine ModelMaterial NumberMaterial GroupRepair Cost
AW51005-01EEC80
BW51005-02EEC85
CW31005-03DIVIDER135
DW31005-04DIVIDER3005
EW51005-05EEC1115
FW21005-08EEC250
AW51005-09EEC350
AW51005-03EEC135
AW51005-25DIVIDER125
BW31005-2DIVIDER125.5
BW151005-3EEC135
RW51005-2EEC450

Thank youuu!

AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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
Posts: 37
Registered: ‎09-20-2018

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!

AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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? 

 

AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

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 )