cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Measure not returning values for each specified time interval (skips some)

Hi Folks;

I haven't done any BI work for some time now and find myself at a deficit/dumber as a result, and am in need of some input.

I have a dataset ("P6_Data") containing scheduling information and had created a measure inteded to return the number (count) of calendar days which span the duration of a particular type of activity (what in the below excerpt is termed a "Phase Code").

As you can see in the above example the time span for "9-260 Gypsum Drywal Hang-Walls" (and given other filter inputs e.g. Building, Level, Area, & Sub Area) is 4/8/2015 to 11/30/2015 or 237 calendar days.
The measure I came up with is as follows:

CALCULATE(
COUNTROWS('dCalendar'),
FILTER(
'dCalendar',
'dCalendar'[Date]>=CALCULATE(MIN('P6_Data'[Start]),FILTER(ALL('P6_Data'),COUNTROWS(FILTER('P6_Data',EARLIER('P6_Data'[Matrix Plot Key])='P6_Data'[Matrix Plot Key])))) &&
'dCalendar'[Date]<=CALCULATE(MAX('P6_Data'[Finish]),FILTER(ALL('P6_Data'),COUNTROWS(FILTER('P6_Data',EARLIER('P6_Data'[Matrix Plot Key])='P6_Data'[Matrix Plot Key])))) &&
'dCalendar'[7 Day Cal]=1
)
)

hen the measure is plotted in a matrix visual with months along the x-axis, it returns the expected results, BUT ONLY for select months:

It shows 23 calendar days in April 2015 since the timeline start is 4/8/2015 and there are 23 days left in the month of April etc.  The question is, why is it not plotting the values for May, June, July, September or October 2015?  Looking at the source dataset values, those cover months of April, May, August and November so presumably there is a nexus to the plot (but in that case why no May?).

Let me know if you have any thoughts insights into the issue.  I would like it to plot values consistently and in accordance with the visual's Month-Year filter context.  The strucutre of the data is what you would expect...I have a Calendar table which covers the entire date range and a Month-Year column for displaying data on that level, all the dimensions are coded within the source dataset ("P6_Data") and are here supplied from the relevant dimension tables (nothing out of the ordinary).

Cheers,

Igor

3 REPLIES 3
Helper III

I am wondering if the question perhaps unclear; please let me know if there is an aspect that needs better/fuller articulation.  Thanks!

Helper III

Anyone? Any guesses?

Helper III

If you have any intuition as to the reson for the issue, please advise so I can explore/test.  This issue is likely to come up again so getting to the bottom of it would be great.
I suppose what makes this a bit more challenging is the reference to multiple table columns in the FILTER argument of the CALCULATE function, and the use of EARLIER function in a measure.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors