Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I Have two tables, the first one contains the budget transactions,their date and main accounts with dimensions and the second one contains the actual transactions, transaction dates and their main accounts with dimensions.
The problem is when i am using a slicer to display the transactions according to their dates, when i am using the dates of budget table it is showing only the correct budget transactions and when i am using actual date in slicer it is filtering out only the actual transactions.
I also tried to create a calendar date table and made a relationship with the other two tables but it does not work.
I want a date slicer which filters out both the actual transactions and budget transactions accordingly, which date should i use?
Any help is really appreciated.
Solved! Go to Solution.
Checked your PBIX file.
You are not getting the results because there is a circular/ambiguous relationship. As a result, one of the relationships from the Calendar table is inactive. Refer to the image below.
First, you should delete the relationship between the field "Display Value" in both the table.
Then edit the inactive (dotted line) relationship from the Calendar table and make it active.
It will resolve the issue.
Then create a separate table for "Display Value" and connect the same to the Display Value field for both the tables. This will avoid a circular relationship. Refer to the image below
You should then use the "Display Value" field from the new table in the slicer.
Conceptually that's about it.
Looks okay. But my suggestion is that you create a calculated table for "Display Values" using a formula like this...
DisplayValues = DISTINCT(
UNION(ALL('BudgetTransactions (2)'[DisplayValue]),
ALL(DimensionAttributeValueCombinations[DisplayValue])
)
)
This will pick up the unique display values from both the tables and union them to give you all the distinct display values from both the table. It is up to you based on what you want.
Once you have a calculated table like I mentioned above with all the distinct DisplayValues from both the table, you will be able to calculate the Variances between Budget and Actual easily because the relationships will become 1 to Many.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |