Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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.     ...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago
// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        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]
  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        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.