Desktop

[iamtrangdoan]

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!

[AlB · Accepted solution]

@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]

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],
        ", "
    )

[iamtrangdoan]

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]

@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],
        ", "
    )

[iamtrangdoan]

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!

[AlB]

@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],
        ", "
    )

[iamtrangdoan]

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 · Accepted solution]

@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]

@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
)