Forum Discussion
DAX
Need help with DAX
Let say I have two tables
Table 1- Company
Now Each company can have multiple assets
Let say
Company Assets
C1 A1
C1 A2
C1 A3
C2 A1
C2 A2
and so on......
Now I have another table
Table 2- Resource and it is already joined using a primary key
Here I only have Assets and Resources.Again 1 Asset can have multiple resources
Assets Resources
A1 R1
A1 R2
A1 R3
A2 R1
A2 R2
and so on........
The end goal is to calculate for each company there are in total how many resources?
Like for Company C1 we have 3 assests and each asset is having 3 resources so total resources would be 9
I want to create a DAX or measure to calculate this
Ideally in SQL maybe I can write like:
select count(Resources) from Resource group by Company, company.companyname group by Resources
- Anonymous4 years ago
Hi Anonymous ,
You could use this formula:
Measure = CALCULATE ( COUNT ( Resource[Resources] ), FILTER ( Resource, Resource[Assets] IN VALUES ( Company[Assets] ) ) )Best Regards,
Jay
6 Replies
- Jos_WoolleySolution Sage
Hi,
When you say "Table 2- Resource and it is already joined using a primary key" what exactly do you mean? You mean it has a relationship with Table 1? Or with some other table?
Regards
- AnonymousNot applicable
Yes correct, It already has a relationship mapped by Project ID to Table 1.
All I am looking for is a DAX that can help me aggregate the columns present in Table 2 for Company Column Table 1
- Ashish_MathurSuper User
Hi,
Share the download link of your PBI file with the relationship already created. Share dummy data if you wish to.
- AnonymousNot applicable
Hi Anonymous ,
You could use this formula:
Measure = CALCULATE ( COUNT ( Resource[Resources] ), FILTER ( Resource, Resource[Assets] IN VALUES ( Company[Assets] ) ) )Best Regards,
Jay