Forum Discussion
Comparing values from two different table on table visual
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) )
I'm adding the file here:
https://filetransfer.io/data-package/slIAQNha#link
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)
)
It's working ...
the thing that bothers me the most is not understanding the behavior of my measure.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.