Different result with measure in Table visual and Dax query

[057Sophie]

Hello folks, 

I am again struggling with some Dax issues.

I have two measures:

MEASURE 1: 

VAR _Test= CALCULATE(CONCATENATEX('01 Chassis Cab',
if(SEARCH(FIRSTNONBLANK('01 Chassis Cab'[Feature code],1),SELECTEDVALUE('MBOM Extract Code Matrix'[Variant Formula]),,0)<>0,'01 Chassis Cab'[Feature code],"")),'01 Chassis Cab'[Behavior]="include") 
RETURN
if(_Test <> BLANK(), "yes","no") 

MEASURE 2:

VAR _Test= CALCULATE(CONCATENATEX('03 MY25 FC',
if(SEARCH(FIRSTNONBLANK('03 MY25 FC'[Feature code],1),SELECTEDVALUE('MBOM Extract Code Matrix'[Variant Formula]),,0)<>0,'03 MY25 FC'[Feature code],"")),'03 MY25 FC'[Behavior]="include") 
RETURN
if(_Test <> BLANK(), "yes","no")

BOTH measures work fine when draged into a table visual.

When I go to DAX Query viewer, ONLY measure 2 is always bringing "no" as result.

EVALUATE

    SELECTCOLUMNS('MBOM Extract Code Matrix','MBOM Extract Code Matrix'[Index],'MBOM Extract Code Matrix'[Item ID],'MBOM Extract Code Matrix'[Variant Formula],

    "MY242 Excluded",[MY24.2 excluded feature],

    "MY242 included",[MY24.2 included feature],

    "MY242 specific",[MY24.2 Specific line],

    "Chassis Cab excluded",[CC excluded feature],

    "Chassis Cab included",[CC included feature],

    "Chassis Cab specific",[CC Specific line],

    "MY25 excluded",[MY25 excluded feature],

    "MY25 included",[MY25 included feature],

    "MY25 specific",[MY25 Specific Line],

    "MY26 excluded",[MY26 excluded feature],

    "MY26 included",[MY26 included feature],

    "MY26 specific",[MY26 Specific Line],

    "MY27 excluded",[MY27 excluded feature],

    "MY27 included",[MY27 included feature],

    "MY27 specific",[MY27 Specific Line],

    "project",[Master Project BL])

My first idea was: the selectedvalue used in the measure only works on the visual (row context?) and not by evaluating a temp table. 

But then... why only one measure is not working and the other yes as they are all written with selectedvalue?

My target was to get a temp table with the "poject" column (measure is derived from the upper measures I am displaying in DAX query just to check the results) so that I could later group by Item ID and get the min from the project column from the temp table.

I haven't come so far as the Temp Table is already not clean.

THX folks

[057Sophie]

I will try your proposal and come back to provide some feedback. Thank you

[123abc]

It seems like the issue might be related to the context in which the DAX query is being evaluated and how the relationships between tables are affecting the results. The behavior you're describing, where one measure works in a table visual but not in a DAX query, can be caused by differences in row context and filter context.

The SELECTEDVALUE function is highly dependent on the context it's used in. It relies on a single value being selected in the column within the current context. In a table visual, the row context is established by the rows in the table, and SELECTEDVALUE can work as expected. However, in a DAX query, the context might be different, leading to unexpected results.

Without having the full data model and understanding the relationships between tables, it's challenging to pinpoint the exact issue. However, I can suggest a few general tips and potential solutions:

1. Check Relationships: Make sure that there are proper relationships established between the tables involved in your DAX measures. Relationships play a crucial role in determining the context in which calculations are performed.

2. Use RELATED: Instead of relying on SELECTEDVALUE, you might consider using the RELATED function to traverse relationships and retrieve values from related tables.

3. Evaluate Measure Independently: Try evaluating each measure independently in the DAX query to see if they produce the expected results. This can help isolate the issue to a specific measure or context.

4. Row Context and Row Context Transition: Be aware of how row context and row context transition work in DAX. Understand the difference between row context and filter context, as well as the impact of row context transition functions like CALCULATE.

Here's a modified version of your DAX query to demonstrate the use of RELATED:

EVALUATE
SUMMARIZE(
'MBOM Extract Code Matrix',
'MBOM Extract Code Matrix'[Index],
'MBOM Extract Code Matrix'[Item ID],
'MBOM Extract Code Matrix'[Variant Formula],
"MY242 Excluded", [MY24.2 excluded feature],
"MY242 included", [MY24.2 included feature],
"MY242 specific", [MY24.2 Specific line],
"Chassis Cab excluded", [CC excluded feature],
"Chassis Cab included", [CC included feature],
"Chassis Cab specific", [CC Specific line],
"MY25 excluded", [MY25 excluded feature],
"MY25 included", [MY25 included feature],
"MY25 specific", [MY25 Specific Line],
"MY26 excluded", [MY26 excluded feature],
"MY26 included", [MY26 included feature],
"MY26 specific", [MY26 Specific Line],
"MY27 excluded", [MY27 excluded feature],
"MY27 included", [MY27 included feature],
"MY27 specific", [MY27 Specific Line],
"project", RELATED('YourProjectTable'[Master Project BL]) -- Use RELATED to traverse relationships
)

Replace 'YourProjectTable' with the actual name of the table that contains the 'Master Project BL' column.

By explicitly using RELATED, you are instructing DAX to follow the relationships between tables to retrieve the desired values.

If the issue persists, further investigation into the data model and relationships may be required.