Forum Discussion

Chandrashekar's avatar
Chandrashekar
Icon for Resolver III rankResolver III
4 years ago
Solved

Relationship :

Hello Team,

 

Kindly help me with measure for below formula

1. Table1 - Created relationship(Table1(Date_Open) with Table2(Date_Open) and Table2 is connected to slicer.

2. I need to calcualte date_closed and it does not have relation with Table2

3. Can we create relationship where we do not have relationship with other table.

ClosedTickets_Day =
var selectedqueue= selectedvalue(Table1[Que])
VAR selecteddate=SELECTEDVALUE('WD'[Curweek])
RETURN
CALCULATE(countrows(Table1),
FILTER(all(Table1),Table1[Date_closed]=selecteddate && Table1[Queue]=selectedqueue
))

  • smpa01's avatar
    smpa01
    4 years ago

    Chandrashekar  Lets's review the set-up first.

     

    1. T2 has a relationship with T1 and values from T2 are used in a slicer to slice T1 values.

    2. T2[Date] has an active connection (1-M) T1[SDate].

    3. The axis for this report comes from T3 which are neither connected to T1 or T2.

    4. The ask is, how can calculation performed on T1 be shown against T3 axis while T2 still slices the calculation.

    5. One of the many ways in DAX, TREATAS helps in JOIN for tables that are not in a relationship. SO the solution takes advantage of that.

    6. Since you need to show T1[CDate] Count too, an inactive relationship between T2[Date] to T1[CDate] (1 to M) was established. Else, the calculation for _close will not get sliced.

     

    Please find the attached pbix and you need the following measures

    
_open =
CALCULATE (
    COUNT ( Table1[SDate] ),
    TREATAS (
        SUMMARIZE ( Table3, Table3[Name], Table3[Location] ),
        Table1[Name],
        Table1[Location]
    )
)


_close =
CALCULATE (
    COUNT ( Table1[CDate] ),
    USERELATIONSHIP ( Table2[Date], Table1[CDate] ),
    TREATAS (
        SUMMARIZE ( Table3, Table3[Name], Table3[Location] ),
        Table1[Name],
        Table1[Location]
    )
)

     

     

  • Chandrashekar's avatar
    Chandrashekar
    4 years ago

    Hello,

     

    I tried to add one more condition to it (Attached Report ) but not getting correct count.

    _close =
    var selecteddate=SELECTEDVALUE(Table2[Date])
    VAr code="A1"
    Return

    CALCULATE (
    countrows(Table1),
    FILTER(all(Table1),Table1[CDate]=selecteddate && CONTAINSSTRING(Table1[Code],Code)),
     
    Regards,
    Chandrashekar B

9 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    You can indeed "create a relationship" where there is none. Either you acn use passive relationship and USERELATIONSHIP or you can use similar DAX as with your example.

    Here is one example where I do just this the differences between out DAX is that I don't use FILTER or "&&":

    Incoming/outgoing =
    var _m =SELECTEDVALUE(Incoming[Month])
    var _p = SELECTEDVALUE(Incoming[Position])

    var _value = calculate(SUM(IncomingFact[Incoming])-SUM(IncomingFact[OutGoing])

    ,all(IncomingFact),IncomingFact[Month]=_m,IncomingFact[Position]=_p)
    return

    _value
     
    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!