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
Thanks for the reply.
Let me explain my model
I have 3 tables
1. Sales (Product, Transaction_Date, Sales_Amount)
2. Targets(Product, Transaction_Date(last day of month), Target_Value)
3. Date(Date, FY, Qrtr, MonthName, Year)
I have created relationship (filter direction = Both)from Sales to Date and Targets to Date(Filter direction=Both)
I am just showing the data in a table like ProductName, MonthName, Year, Sales_Amount, Target_Value
Everything is fine with Sales but Targets are aggregated to whole products
Example,
| Product | Sales | Target | Actual Target |
| Cycles | 87000 | 100000 | 70000 |
| Bikes | 25000 | 100000 | 15000 |
| Cars | 40000 | 100000 | 15000 |
| 152000 | 100000 | 100000 |
Why am I not getting correct Target value?
Cheers
Hi,
The relationship between Target and Sales table is missing hence you are not getting the filtered values for Target.
Also, in my experience, single filter direction works more effeciently. Ideally, you will use Date table to filter the Sales and Target table and not vice versa.
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.