Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I want to filter multiple measures in my report using single date dimension ,
my report has below tables
Tickets
TicketID, CreatedDate,
1 2021-07-01
2 2021-07-02
3 2021-07-03
4 2021-07-04
5 2021-07-05
TicketAction
ActionDate, ActionStatus ,TicketID
2021-07-01 Inprogress 1
2021-07-02 Awaiting Reply 1
2021-07-02 Resolved 1
2021-07-04 Inprogress 2
2021-07-05 Awaiting Reply 2
DateTable
CalendarDate
2021-07-01
2021-07-02
2021-07-03
2021-07-04
2021-07-05
2021-07-06
2021-07-07
2021-07-08
2021-07-09
2021-07-10
when the user selects the date , I want to filter out tickets which are created between those dates and also only the ones which are actioned between those dates .
in this specific case in below screenshot ,slicer selected is 1stjuly to 2ndJuly, tickets created between 1st July and 2nd July is 2 which is correct
but the tickets actioned between 1st July and 2nd July is only one ,but report shows two .
since there is a passive relationship(indicated by dotted lines) between ActionDate and CalendarDate it is always filtering on createddate not by actiondate.
I tried by creating a measure using USERRELATIONSHIP , which works only for one case , but I have multiple other slicers( Like team who worked, Region they belong to ) in the report , user relationship works only on one specific table, when I use other silcers count again mismatches
can someone help on how to use one single date slicer to filter multiple measures.
Solved! Go to Solution.
Hi @avinash2807
Your model is (close to) AMBIGUOUS. Please watch/read this first: Bidirectional relationships and ambiguity in DAX - SQLBI
To make your model work you have to get rid of this ambiguity. Since you have not shown the measures, I assume that in the one that uses USERELATIONSHIP you do not disable the connection between Tickets and FiscalYearTable. So, you've got an ambiguous model in such a case because then there are 2 paths from FiscalYearTable to the TicketAction table and the engine must select one of them. Obviously, it selects the wrong one from your point of view. Therefore, you have to make sure that your measure disables the connection to Tickets via the directive CROSSFILTER (the first directive in your measure is USERELATIONSHIP) -> CROSSFILTER(..., ..., None).
By the way, please change the 1<->1 relationship between FiscalYearTable and Tickets to one-way only, from FiscalYearTable to Tickets. You should never have to use 1<->1 in your models. This almost means the design is flawed. If such a connection exists, it means you should consolidate the data on both ends into a single table.
Hi @avinash2807
Your model is (close to) AMBIGUOUS. Please watch/read this first: Bidirectional relationships and ambiguity in DAX - SQLBI
To make your model work you have to get rid of this ambiguity. Since you have not shown the measures, I assume that in the one that uses USERELATIONSHIP you do not disable the connection between Tickets and FiscalYearTable. So, you've got an ambiguous model in such a case because then there are 2 paths from FiscalYearTable to the TicketAction table and the engine must select one of them. Obviously, it selects the wrong one from your point of view. Therefore, you have to make sure that your measure disables the connection to Tickets via the directive CROSSFILTER (the first directive in your measure is USERELATIONSHIP) -> CROSSFILTER(..., ..., None).
By the way, please change the 1<->1 relationship between FiscalYearTable and Tickets to one-way only, from FiscalYearTable to Tickets. You should never have to use 1<->1 in your models. This almost means the design is flawed. If such a connection exists, it means you should consolidate the data on both ends into a single table.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |