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
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
))
Solved! Go to 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]
)
)
Hello,
I tried to add one more condition to it (Attached Report ) but not getting correct count.
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 "&&":
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.
Proud to be a Super User!
@Chandrashekar please provide sample data/sample pbix
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]
)
)
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |