Aggregate Child Rows

[ChrisJC]

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]

)

)

[MFelix]

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]

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]

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_mZkdHuswmnmMPRL...

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.

[ChrisJC]

Hi @v-kkf-msft 

Thanks for taking a look. My problem is dsg_databasename is not the same for each account.

I have created this PowerBI with some dummy data to hopefully help:

https://1drv.ms/u/s!AlsfqodhYbavibIHl5NuBM-y98P_pw?e=XM36HW

Thanks,

Chris

EDIT:

You powerbi is the same as mine. To give a better idea of what I'm expecting (in red) by selecting different slicer options

[MFelix]

Hi @ChrisJC ,

Looking into the data you are presenting you want to calciulate the number of DB if they have included a product selected, so inf this case believe that the answer of @v-kkf-msft  should do the trick.

[v-kkf-msft]

Hi @ChrisJC ,

Try the following formula:

Number of Databases = 
CALCULATE(
    COUNT(database[databaseid]),
    FILTER(
        ALL(database),
        database[accountid] = MAX(accounts[accountid])
        && database[product id] = MAX(database[product id])
    )
)

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.

[ChrisJC]

Hi @v-kkf-msft 

Thanks for the quick response this morning.

It's almost there, but it's counting per product, where as I want it across all selected products:

I have tried to use the selected columns but think I'm back to one of my earlier issues where it cross joins or something:

Number of Databases = 
VAR select_product = VALUES('product'[productid])
RETURN
 CALCULATE( 
 COUNT(database[databaseid]), 
 FILTER( 
 ALL(database), 
 database[accountid] = MAX(accounts[accountid]) 
 //&& database[product id] = MAX(database[product id]) 
 && database[product id] IN select_product
 ) 
 )

[v-kkf-msft]

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(
        ALL(database),
        database[accountid] = MAX(accounts[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.

[ChrisJC]

Hello @v-kkf-msft 

EDIT: Sorry I selected the solution prematurely.

This works when all products are selected as in your screen shot. However when not all products are selected, it's showing all for the account.

I'm hoping to get this:

Also, my main lack of knowledge was on the two way filtering.

If you have the time, could you explain why the database -> account and database -> product filters are required for this?

Kind regards,

Chris

[v-kkf-msft]

Hi @ChrisJC ,

This document will help you better understand the bi-directional relationship.

Bi-directional relationship guidance - Power BI | Microsoft Docs

[ChrisJC]

Hi @v-kkf-msft 

I believe this is working - when I select products on the slicer, it shows me the total DBs for the selected product(s) for each account:

Number of Databases = 
VAR selected_products = ALLSELECTED('product'[productname])
RETURN
    CALCULATE( 
        COUNT(database[databaseid]), 
        FILTER( 
            ALL(database),
            database[accountid] = MAX(accounts[accountid]) 
            && RELATED('product'[productname]) IN selected_products
        )
    )

Please let me know if you see any error or whether it could be improved.

Thanks for you help in getting me this far!

Kind regards,

Chris

[v-kkf-msft · Accepted solution]

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.