Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ChrisJC
Helper I
Helper I

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:

ChrisJC_0-1615395886254.png

 

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]
)
)

 

1 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])
    )
)

 

image.png

 

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.

 

View solution in original post

12 REPLIES 12
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

image.pngimage.png

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.

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

ChrisJC_0-1615799860528.png

ChrisJC_1-1615799938087.png

ChrisJC_2-1615800014407.png

 

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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])
    )
)

v-kkf-msft_0-1615800410176.png

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.

 

 

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:

ChrisJC_12-1615804271320.png


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
 ) 
 )

Hi @ChrisJC ,

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

image.png

 

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

 

image.png

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.

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:

ChrisJC_1-1615886543949.png

 

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

Hi @ChrisJC ,

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

Bi-directional relationship guidance - Power BI | Microsoft Docs

 

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

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])
    )
)

 

image.png

 

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.