Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a date dimension table and a fact table with ticket information - there is a relationship between the Date table and the Created Date from the fact table.
On a specific page, I have
-multiple tables with Date (from the dimension table) and measures (from the fact table) and
-a Date slicer (from the dimension table).
Some of these measures should be filtered by
-Date (from the dimension table) as defined by the active relationship
-others should be filtered by the same Date (from the dimension table), but use a different "relationship", namely Date - Closed Date
I have adjusted the DAX formula (below script) for the second group of measures and the table visual displays information as intended - measures by Date (from the dimension table), where Date actually refers to Closed Date, not Created Date.
However, when I manually select specific months from the Date slicer, the table visual ignores my adjustments in DAX and goes back to using the active relationship.
Is there any way to further adjust the DAX formula so that it fully ignores the active relationship, both inside the visual and inside the slicer?
# Tickets with Positive Feedback =
var ctx_date = values('[F] Date'[Month])
return
CALCULATE(
DISTINCTCOUNT(Incidents[Number]),
FILTER(
//Incidents,
ALLSELECTED(Incidents),
Incidents[completionDate] <> BLANK()
&& Incidents[CompletionDateMonth] in ctx_date
&& Incidents[feedbackRating] in {4, 5}
&& Incidents[CSAT Is Valid for SD] = TRUE()),
CROSSFILTER('[F] Date'[Date], Incidents[CallDate_clean], None)
Thank you!
@ruxandraalina You need to use the USERELATIONSHIP function.
Thanks! I actually tried it before submitting this post, but it's not giving the intended result.
The first two tables use Created Date and Closed Date from the fact table, while the third one uses Month from the dimension table.
To double check the results, I have created an additional simple formula (# Tickets with Pos Feedback v2) that is not connected to the relationship. The point was to double check whether the results in the middle table or equal to the results in the last table, but they are not.
Is there anything wrong in my DAX formula for the third table?
# Tickets with Positive Feedback =
var ctx_date = values('[F] Date'[Date])
return
CALCULATE(
DISTINCTCOUNT(Incidents[Number]),
USERELATIONSHIP(Incidents[CompletionDate_clean], '[F] Date'[Date]),
FILTER(
Incidents,
//ALLSELECTED(Incidents),
Incidents[completionDate] <> BLANK()
&& Incidents[CompletionDate_clean] in ctx_date
&& Incidents[feedbackRating] in {4, 5}
&& Incidents[CSAT Is Valid for SD] = TRUE()
//&& USERELATIONSHIP(Incidents[CompletionDate_clean], '[F] Date'[Date])
//CROSSFILTER('[F] Date'[Date], Incidents[CallDate_clean], None)
//REMOVEFILTERS('[F] Date')
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
84 | |
77 |