Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous

 

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

8 REPLIES 8
AlB
Super User
Super User

Hi  @Anonymous

 

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

 

Anonymous
Not applicable

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?

 

 

@Anonymous

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

 

 

Anonymous
Not applicable

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!

@Anonymous

 

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

 

Anonymous
Not applicable

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!

@Anonymous

 

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? 

 

@Anonymous

 

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 )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.