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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
fpennisi
Regular Visitor

Measure causes rows in a table to multiply

Hi,

In the report I am creating, I have 15 minutes data across different units.

There is a time dimension data goes from the 15 minutes to the hour and then to the day -> month -> year.

There is an asset hierarchy that goes from a unit to a site -> region -> country and so on.

The fact table reporting data is linked from one side to the 15 minute intervals table and on the other side to the unit table.

 

Not all the units started to log data at the same time.

 

I want to calculate how many samples are expected from a unit since when it started logging to today. This numeber may vary if I restrict the date range, to its min and max date.

 

This is the Measure I created, which performs the calculation as I expect:

 

Unit Hours =
VAR DateFrom = MAX(MIN('Date'[Date]), MIN('Unit'[Data available from]))
VAR DateTo = MIN(TODAY(), MAX('Date'[Date]))
VAR ExpectedHours = CALCULATE(SUM('Date'[Hours in the day]), FILTER('Date', 'Date'[Date] >= DateFrom), FILTER('Date', 'Date'[Date] <= DateTo))
RETURN ExpectedHours
 
In the measure I used the sum of hours in the day since I need to take into account daylight saving, so the days in the time range can have 23, 24 or 25 hours, according to the selection.
 
If I put it in a table which shows the list of units the value are the ones expected and there is one row per unit.
If I add to this table site, region and country fields for the unit, or other fields from the asset dimension, the row numbers increase.
 
How can I prevent that?
 
Thank you
0 REPLIES 0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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