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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Hierarchy help

Hi,

 

Im just wondering if there's possibly a function that would help total up however many lower levels there is in a hierarchy e.g. Building name below and display the total (400).

 

The reason I ask is because the first measure ( Total Bookable Hours) for the 200 below comes from an unrelated date table so it doesn't naturally sum upwards. I almost need the Total bookable hours to display for how ever many occurences of a room there is and display total for the building.

 

The measure reads 

Total Bookable Hours =
CALCULATE(
COUNT(vwDimDate[Date]),
FILTER(vwDimDate, vwDimDate[WorkingDayFlag] = 1)) * 10

 

Ive looked into isinscope but the second part for the building I cant seem to work, and the measure totals dont seem to help.

 

Hope that makes sense

 

roomutilhelp.PNG

 

Thanks

 

Liam

1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 

It looks like the same thread there.

So I directly tested the pbix  you  provide there.

It is unreasonable to establish relationship between tables through the field "Date" and  "Start date"

14.png16.png15.png

 

Through your original formula, I guess  you may just want to get the result of   [count of  all working days in the date] * 10.

If it is ,  try  this measure as below:

Measure = 
SUMX(GROUPBY('Bookings','Bookings'[Building Name],'Bookings'[Room]),COUNTROWS(FILTER(DISTINCT('Bookings'[Start Date]),WEEKDAY([Start Date],2) IN {1,2,3,4,5})) *10 )

pbix attached 

 

Best Regards,
Community Support Team _ Eason

 

 

 

View solution in original post

7 REPLIES 7
v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 

It looks like the same thread there.

So I directly tested the pbix  you  provide there.

It is unreasonable to establish relationship between tables through the field "Date" and  "Start date"

14.png16.png15.png

 

Through your original formula, I guess  you may just want to get the result of   [count of  all working days in the date] * 10.

If it is ,  try  this measure as below:

Measure = 
SUMX(GROUPBY('Bookings','Bookings'[Building Name],'Bookings'[Room]),COUNTROWS(FILTER(DISTINCT('Bookings'[Start Date]),WEEKDAY([Start Date],2) IN {1,2,3,4,5})) *10 )

pbix attached 

 

Best Regards,
Community Support Team _ Eason

 

 

 

Anonymous
Not applicable

Hi @v-easonf-msft ,

 

My apologies, I thought it had been deleted. Thankyou so much for your time and the measure.

 

Regards

 

Liam

amitchandak
Super User
Super User

@Anonymous , refer if this can help

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi @amitchandak ,

 

Thanks for your reply. This article relies on the measure summing upwards naturally which unfortunately my measure doesnt do.

 

Thanks

 

Liam

Anonymous
Not applicable

Hi @amitchandak ,

 

Thanks again for replying. They are all using the SUM function from the same table which I'm unfortunately not.

 

I'm not sure whether its possible, as if I use the isfiltered or isinscope it will always equal the same total as the lower level. I was hoping of an alternative approach where it could total the lower level on the fly

 

I tried like this

When Filtered = if( ISINSCOPE('Item'[Category]),SUM(Sales[Net Sales]),MIN(Sales[Net Sales]))

 

Screenshot 2020-05-07 20.02.17.png

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.