Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Chandrashekar
Resolver III
Resolver III

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

2 ACCEPTED SOLUTIONS

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

 

smpa01_0-1642513203429.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

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

View solution in original post

9 REPLIES 9
ValtteriN
Super User
Super User

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello,

is it possible without creating relationship?

Regards,

Chandrashekar B

 

Hi @Chandrashekar ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Chandrashekar  please provide sample data/sample pbix

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hello, 

will share it by today.

 

Regards,

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

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

 

smpa01_0-1642513203429.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hello,

 

Thanks for your detailed explation and issue got solved. 

Regards,

Chandrashekar B

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.