Forum Discussion
Get table total as column while having filter condition from related table slicer (ALLSELECTED)
Consider 2 Tables:
Fact Table:
| Policy | Value |
| A | 100 |
| B | 200 |
| C | 300 |
| D | 400 |
| E | 500 |
Dimension Table:
| Policy | Region |
| A | US |
| B | UK |
| C | EMEA |
| D | US |
| E | UK |
Both tables related on key - "Policy"
For my table visual, I would like to calculate a simple measure "New Measure" which gives me the total of the entire table as a separate column. As such:
| Policy | Value | New Measure |
| A | 100 | 1500 |
| B | 200 | 1500 |
| C | 300 | 1500 |
| D | 400 | 1500 |
| E | 500 | 1500 |
Measure Used:
However, I have region (from dimension table) as slicer in my report page. This is somehow breaking the table and returning all rows
Above screenshot, can see even on selecting EMEA, I get all five records. I have a feeling it is doing some sort of cartesian join when using ALLSELECTED.
Expected Behavior: I would like the region slicer to work but see the table total as a different column.
How can I achieve this?
Hopefully, question makes sense - happy to provide more details. Thank you.
Note: Example has been simplified - original use case has multiple dimension tables and fact table has a few million records. Hence, I would need a scalable solution.
Pbix: Link
Edit: I would need to have the Policy field in my final visual come from table - FACT (not DIM). The original relationship between two is not on this field hence they're not interchangeable.
hello dogba
i might be misunderstood but i am using your DAX but get different result with EMEA.
Have you created a relationship between fact and dim table?
it is one-to-one in my screenshot, but in your original table it most likely one-to-many from dim to fact table.
Hope this will help.
Thank you.
Hi dogba
Thank you for reaching out microsoft fabric community forum.May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
9 Replies
hello dogba
i might be misunderstood but i am using your DAX but get different result with EMEA.
Have you created a relationship between fact and dim table?
it is one-to-one in my screenshot, but in your original table it most likely one-to-many from dim to fact table.
Hope this will help.
Thank you.
Thank you for your response - can you see my reply above?
I think my question was unclear - the Policy field must come from FACT table in the final table visual as the original relationship is on a different category field.
I will update my question but hopefully it is clearer this time - is this possible to achieve?hello dogba
if you want to filter out a value like you have shown in expected behaviour, your need a value in table visual that has a relationship from your dim tabl.
from your updated question, seems there is no relationship between your sample data (the relationship goes to another column which is not shown in sample data).
please share a sample data that represent your original data.
there might be a way around to achive your requirement (consume Policy from fact tbl).
Thank you.
Hi,
These images should clarify
Thank you for your response. Your solution works but in my table visual, I need the field Policy to come from FACT table (not DIM). Realise my example may have been a little too simple as my original relationship is not on the policy field but a different category. As such, this would not allow me to use Policy field from the region dimension table.
This is what I am getting on using Policy from FACT table (note how measure returns row total and not overall total)
Is there any way to achieve this?You are welcome. That is not the right table to drag it from. That field should be dragged from the Dim table.
Hi dogba
Thank you for reaching out microsoft fabric community forum.May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
