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)
)

     

johnt75's avatar
johnt75
Super User
3 years ago

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'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.