Forum Discussion
Suggest the data model for my data
In such cases, I usually create a simple table of unique products and then link it with Product and Target. This table then will act as filter table and you can use it in your visuals and DAX.
So you will have two filter tables: Product(Unique) & Calendar/Date table
And you will have two fact tables: Performance Data and Target Data
There will be a Many-to-One relationship between fact and filter tables and then you can write your DAX suitable to your need. You can use RELATED function to filter the facts table.
Hope this should help.
Rgds,
Vivek
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Thank you for the reply.
I've changed the filter direction to single from Sales to Date and Date to Target
Actually, there is no direct relationship between Sales and Targets unless Product. But, it should be Many-to-Many
I am confused how to create measure to get the trget value.
If I directly pull Target_Value from Targets table and Product and Sales_Amount from Sales table, I am getting the target_value as explained in my previous post.
Do I need to write DAX expression for Target_Value? If so, in which table I need to create measure?
What could be the DAX expression?
Please advise me
Cheers
In such cases, I usually create a simple table of unique products and then link it with Product and Target. This table then will act as filter table and you can use it in your visuals and DAX.
So you will have two filter tables: Product(Unique) & Calendar/Date table
And you will have two fact tables: Performance Data and Target Data
There will be a Many-to-One relationship between fact and filter tables and then you can write your DAX suitable to your need. You can use RELATED function to filter the facts table.
Hope this should help.
Rgds,
Vivek
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.