DAX / Measure help on volume for multiple projects during different dates

Hi carlenb this issue was reason for my question, what is fact what is dimension tablee (table 1 and project)

Put all projects in one tabl table which should be dimension, other should be fact and change type of relationship. This is best practice. Many to many is advance topic, not suitable currently.

Hi carlenb now when we have Project table, what other tables / data you have in your model and what you want to be calculated? 

Today we are at start 😕

Hi carlenb I would ask you to think again about your model / example you provide.

Reasoning:

Example should reflect your REAL actual data, possible combination - for sure you can hide sensitive data, and details but actual data should be provided to model / provide solution. 

In your example, in table 2 there are many projects comparing with table 1, this cause relationship creation and possible solution.

I really tried to understand your data and provide solution, but you did not provide sound example with output.

Please, put your actual data and process in tables, describe and replace it with some names to hide sensitive data... Maybe this is hard / new to you but will benefit you in long run.

Hope you understand.

Hi carlenb 

Take a look closely at least two issues with current example. I would say, granularity of data in your example should be adjusted either change table 1 or table 2.

Hi carlenb  I will check it and let you know

Hi some_bih 

Very thankful for all your support and help on this. I got it to work thanks to all of your patience! You're officially my hero 🙂 

Thanks a lot! 

Hi some_bih thanks for the patience with me 🙂 

What I did was to create a test dimension table for Project, containing only 1 project. I got this relationship to work, RELATED works and the calculations works as well. 

The problem is that in reality there will not be unique values in the project table. Maybe I was bad at explaining this in the beginning, sorry for this in that case. In reality the project table looks liks this: 

So for this reason I can't avoid having duplicates in the project table and 1:1 relationship will not work. 

Does this make the case more clear and how would you solve it? 

Hi some_bih, let me try to clarify, again thanks for your patience 🙂 What's needed is to calculate the total sales volume for each project, and then again multiply this against the number of projects. Projects will start at different starting dates. So the end result will be a graph where you can filter on different projects or dates/years and see the total value for that. 

• Table 1: Column A = Project name, B = Sub project name, C = Article number, D = Price, column E= Quantity
• Table 2 with column A = Project number, B = Project name, C = Sub project name, D = number of projects E= start date for projects
• Calender table

Table 1 looks like this 

So table 1 will be unique in project name and sub project name. I will add more tables like table 1 over time, one table for each individual project. In the picture I have the project "CTV PPC" as an example. But I will create more tables for the projects "CTV KGH", "ABC YUL", "ABC TUI" etc. as seen in table 2. 

Table 2 looks like this

Hi some_bih, thanks for getting back. 

I'm sorry but I don't understand what you need. Let me explain:

• The example I have provided is exactly the tables I have, I have only swapped the real data to dummy data. The only difference from real life is that I have excluded columns not relevant at all for the calculation such as currency, unit columns etc.
• The RELATED solution you have provided above do in fact work when there are unique rows (project names) in table 2, I tried this and I get the expected output value for that project. But in my data this is not the case because for each row in Table 2 there will be a project name and a project start. So each project name will occur multiple times and not a single time. This must also match on the sub-category project. Hence my understanding is that I can only create a many to many relationship, which is causing RELATED not to work. 
• Based on this and the data I actually have in front of me: what should I do?  

Hi some_bih you are going to be my future hero after all this help 🙂 

It is fine to retrieve on an overall level and include all articles. So for clarity the output for CTV PPC would be: 

• Value for CTV PPC will be Price * Quantity = 200 (Retrieved from Table 1) 
• Total value for CTV PPC in 2024-01-01 will be 200 * 2 = 400 (Table 2) 
• Total value for CTV PPC in 2024-02-04 will be 200 * 2 = 400 (Table 2) 

If it makes it easier to see the data I also created a duplicate of the model, you can see it here: 

https://drive.google.com/file/d/1xVMSn-auYJJVGX2s26YjHeDK51hCNYT6/view?usp=drive_link

In this case I also added another project so that you can see how it works for more data. 

Still learning, I understand sharing the model earlier would've been good 🙂 

Hi some_bih very appreciated, thanks!! 

Hi carlenb if this is ok for you accept it as solution, so other member of community could use it.

Hi carlenb I really happy when I could help.