Forum Discussion

ChrisJC's avatar
ChrisJC
Helper I
5 years ago
Solved

Aggregate Child Rows

I've come across this scenario a couple of times but haven't been able to solve it.

 

I have 3 tables in my model:

 

I have a slicer based on products[c20_name]

I have a table that has the following columns:

accounts[account name], databases[databasename], [number of databases]

 

[number of databases] is a measure that attempts to calculate the number of databases for the account within the products filter. However I always seem to either count the number of databases as 1 (grouping on the database row filter context), all databases across all accounts, or cross-joining for the selected products.

 

This is my closest effort, it attempts to count the number of dsg_databasesid values (unique for each row) removing the row filter on that column, but keeping the row filter on _dsg_databaseaccount_value and silcer filter on _dsg_product_value:

Number of Databases =
CALCULATE(
COUNT(databases[dsg_databasesid]),
ALLEXCEPT(
databases,
databases[_dsg_databaseaccount_value],
databases[_dsg_product_value]
)
)

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi ChrisJC ,

    First, you need to change the relationship between the tables to Both, and then modify the measure Number of Databases.

     

    Number of Databases = 
CALCULATE(
    COUNT(database[databaseid]),
    FILTER(
        ALLSELECTED(database),
        database[accountid] = MAX(database[accountid])
    )
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

12 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi ChrisJC ,

     

    Not really sure if I'm understanding what you want to achieve I made a sample data base with a similar model to yours and making a simple cout of the DATABASEID I was abble to count the number of databases based on product filtering and account, no need to do any special calculation.

     

    Can you please elaborate a little bit better what is the expetced result and please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • ChrisJC's avatar
      ChrisJC
      Helper I
      5 years ago

      Hi Miguel

      Thanks for taking a look in to this.

      My problem is when I add a column from the DATABASE table, the count of database rows will return 1, where as I want to ignore the database context and keep the account context and product filter.

      I will PM you a mock up.

      Thanks,

      Chris

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi ChrisJC ,

        Based on my understanding, I created the following sample data. And count the database names in the visual. Can you provide more sample data and the output results you want if it does not meet your expectations?

        _dsg_databaseaccount_value _dsg_product_value dsg_databasename
        1 1 DB1
        1 1 DB2
        2 1 DB3
        3 1 DB4
        2 2 DB5
        2 2 DB5
        2 2 DB5
        4 2 DB6

        This is my PBIX file.

        https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EXTzP4_mZkdHuswmnmMPRLEBERRWBmkPlC6_1gvHvS-HZQ?e=Xz6SuS

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.