Relationship :

[Chandrashekar]

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 · Accepted solution]

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

[ValtteriN]

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!

[Chandrashekar]

Hello,

is it possible without creating relationship?

Regards,

Chandrashekar B

[ValtteriN]

Hi @Chandrashekar ,

The DAX I used in my example doesn't require relationship between the tables.

[smpa01]

@Chandrashekar  please provide sample data/sample pbix

[Chandrashekar]

Hello, 

will share it by today.

Regards,

Chandrashekar 

[Chandrashekar]

Hello, 

Please find sample file Sample Report. 

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

Regards,

Chandrashekar B

[smpa01 · Accepted solution]

@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]

Hello,

Thanks for your detailed explation and issue got solved. 

Regards,

Chandrashekar B

[Chandrashekar · Accepted solution]

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