Forum Discussion

kchojnowski's avatar
kchojnowski
Frequent Visitor
7 years ago
Solved

Direct query - relationship with multiple columns

Hi,
DQ - connection with SQL Server
I have slicer with calendar. Need to make relationship with table, which has one status column and two date columns: date activation and date deactivation. They can have different values. Something like this: [pic]
Is it possible to count activations and deactivations and group by date and create relationship with calendar table (how make this relationship)?

 

On this example i have:
2018-10-25 one activation/ 2 deactivations
2018-01-01 one activation /0 deactiavtions

 

 

 

 

The final result: after picking on calendar 2018-10-25 i should have on my visualization 1 activation and 2 deactivations

 

Regards, Karol

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi kchojnowski,

     

    You could create below measures:

    count for activation =
CALCULATE (
    COUNT ( StatusTable[Date Activation] ),
    FILTER (
        ALL ( StatusTable ),
        StatusTable[Date Activation] = SELECTEDVALUE ( DateTable[Dates] )
    )
)

count for deactivation =
CALCULATE (
    COUNT ( StatusTable[Date deactivation] ),
    FILTER (
        ALL ( StatusTable ),
        StatusTable[Date deactivation] = SELECTEDVALUE ( DateTable[Dates] )
    )
)

     

    Add DateTable[Dates] and above two measures into a table visual.

     

    Best regards,

    Yuliana Gu

  • kchojnowski's avatar
    kchojnowski
    7 years ago

    Thanks for reply! I have made something like this and it worked:

    CALCULATE(COUNT(<table[columnname]>),USERELATIONSHIP(table[mydate];calendar[date]))

    Ofc before i have made relationship between date activation - calendard date and inactive relationship between date closed and calendar date :)

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi kchojnowski,

     

    You could create below measures:

    count for activation =
CALCULATE (
    COUNT ( StatusTable[Date Activation] ),
    FILTER (
        ALL ( StatusTable ),
        StatusTable[Date Activation] = SELECTEDVALUE ( DateTable[Dates] )
    )
)

count for deactivation =
CALCULATE (
    COUNT ( StatusTable[Date deactivation] ),
    FILTER (
        ALL ( StatusTable ),
        StatusTable[Date deactivation] = SELECTEDVALUE ( DateTable[Dates] )
    )
)

     

    Add DateTable[Dates] and above two measures into a table visual.

     

    Best regards,

    Yuliana Gu

    • kchojnowski's avatar
      kchojnowski
      Frequent Visitor
      7 years ago

      Thanks for reply! I have made something like this and it worked:

      CALCULATE(COUNT(<table[columnname]>),USERELATIONSHIP(table[mydate];calendar[date]))

      Ofc before i have made relationship between date activation - calendard date and inactive relationship between date closed and calendar date :)

      • Rekha's avatar
        Rekha
        Frequent Visitor
        7 years ago

        Multiple relation ship not working in single table. Please suggest how to connect (give) multiple column relationship to single table