Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I created a measure in fact table which uses one field of table 'd_prd_targets' but this measure is giving same total value for any filtered field in fact table. Why there is an issue in data filter between these two tables?
Solved! Go to Solution.
@Anonymous
Your measure is based on a column that belongs to a dim. Table which is 'd_prd_target'. It cannot be filtered by Fact and cannot be filtered by other dim. It is strange that you are slicing by a field from a fact table. Whatever your reason is you can do the following:
Let's say your calculation is SUM ( 'd_prd_target'[Value] ) then in order for this measure to be filtered by the fact table you need to write it
CALCULATE (
SUM ( 'd_prd_target'[Value] ),
CROSSFILTER ( 'FactTable'[Product Key] , 'd_prd_target'[Product Key], BOTH )
)
Hi @Anonymous
your model is totally fine. However let me try to answer your question.
1. It doesn't matter in which table a measure is stored in. It will behave completely the same. It is just stored in that table but it doesn't actually belong to it. Measures belong to the model the same way tables themselves belong to the model.
2. in lieu of obove a measure is evaluated based on the filter context only regardless of which table it is stored in.
3. The measure that you have described in your question is based on a dim. Table. And the filter context is apparently based on other dim. Table(s). In a stat schema, dim. Tables do not filter each other, they only filter the fact Table. Therefore, when a measure is based a dim. Table, extra dax need to be written in order to trasfer the filter from one dim. Table to another.
4. In order to solve your problem you need to provide more context and more of the issue as power bi is all about details and filter context.
Thanks @tamerj1, very helpful as always.
The newly created measure is not getting filtered even based on a field in fact table. In this measure I have just done a simple sum of one field in 'd_prd_target' table. I'm attaching few screenshots for reference.
@Anonymous
Your measure is based on a column that belongs to a dim. Table which is 'd_prd_target'. It cannot be filtered by Fact and cannot be filtered by other dim. It is strange that you are slicing by a field from a fact table. Whatever your reason is you can do the following:
Let's say your calculation is SUM ( 'd_prd_target'[Value] ) then in order for this measure to be filtered by the fact table you need to write it
CALCULATE (
SUM ( 'd_prd_target'[Value] ),
CROSSFILTER ( 'FactTable'[Product Key] , 'd_prd_target'[Product Key], BOTH )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |