Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    4 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

  • 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

    • Anonymous's avatar
      Anonymous
      Not 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_Mathur's avatar
        Ashish_Mathur
        Super User

        Hi,

        Share the download link of your PBI file with the relationship already created.  Share dummy data if you wish to.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    You could use this formula:

    Measure =
    CALCULATE (
        COUNT ( Resource[Resources] ),
        FILTER ( Resource, Resource[Assets] IN VALUES ( Company[Assets] ) )
    )
    

     

    Best Regards,

    Jay