cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Comparing values from two different table on table visual

Greetings,

I'm struggling to find a solution to my problem.

I have two related tables as following:

I'm trying to calculate this measure that i need to insert inside a table visual.

``````var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return

SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)``````

This is the table, as you can see the relation between Table1.key and Table2.key is working as expected.

And this is the result adding the measure.

What exactly I'm not understanding? Of course this is related to switch and if function..... but I'm not getting it.
Any help would be much appreciated

1 ACCEPTED SOLUTION
Super User

There must be a better way of doing this, but the below seems to work

``````Measure =
var Table2Keys = SUMMARIZE( Table1, Table2[_kEY])
var CurrentKey = SELECTEDVALUE(Table1[_kEY])
RETURN IF( CurrentKey IN Table2Keys,
var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return

SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)
)``````

8 REPLIES 8
Super User

Having a bidirectional many-to-one relationship is usually a bad idea, try changing it to a single direction if you can.

Anonymous
Not applicable

and this does not change the result....

if in the measure i m not using the switch or if function, it's working properly.
Any idea??

Super User

Can you copy the code generated for the table visual from Performance Analyzer and paste it in here? Or can you share a PBIX file with any confidential info removed ?

Anonymous
Not applicable
``````// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPGROUP(
'Table1'[_kEY],
'Table1'[Durata],
'Table2'[soglie],
'Table2'[_kEY],
'Table1'[Operazione],
'Table1'[Processo]
), "IsGrandTotalRowTotal"
),
"Measure2", 'Table1'[Measure2]
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Table1'[_kEY],
1,
'Table1'[Durata],
1,
'Table2'[soglie],
1,
'Table2'[_kEY],
1,
'Table1'[Operazione],
1,
'Table1'[Processo],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Table1'[_kEY],
'Table1'[Durata],
'Table2'[soglie],
'Table2'[_kEY],
'Table1'[Operazione],
'Table1'[Processo]``````
Anonymous
Not applicable
Super User

There must be a better way of doing this, but the below seems to work

``````Measure =
var Table2Keys = SUMMARIZE( Table1, Table2[_kEY])
var CurrentKey = SELECTEDVALUE(Table1[_kEY])
RETURN IF( CurrentKey IN Table2Keys,
var a = MAX(Table1[Durata])
var b = MAX(Table2[soglie])
return

SWITCH(TRUE,
a > b , -1,
a < b , 1,
0)
)``````

Anonymous
Not applicable

It's working ...
the thing that bothers me the most is not understanding the behavior of my measure.

Super User

The problem lay in the behaviour of SUMMARIZECOLUMNS and the fact that your measure was not returning blank for invalid combinations of rows from table 1 and table 2.

I don't fully understand the workings of SUMMARIZECOLUMNS, but it looks like it was returning all possible combinations of rows from table 1 & 2, rather than using the relationship, but it will only return combinations where the measures being summarized do not return a blank.

The code I wrote seeks to establish whether the current combination is a valid one, given the relationship, and only return a value in the case of valid combinations.

Announcements