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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jeanxyz
Post Prodigy
Post Prodigy

can't wrap a measure in another measure

Hi,

I have written a long measure which has worked as expected.  A the measure is quite long and some var is used in other measures as well, I decide to take out var weekstart_ and var weekend_ and convert them into measure [weekstart] & [weekend]. I them quote these two measures in a new measure. However,  the new measure [Total Hrs Planned Weekly2] doesn't work here. It doesn't filter data with [weekstart] and [weekend] values. Please help.

 

original measure:
Total Hrs Planned Weekly2 =
VAR year_ = SELECTEDVALUE( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
var current_year_=year(today())
var max_week_= switch(true(),
year_ < current_year_, max(Dim_Date[WeekNumber]), maxx(filter(all(Dim_Date),Dim_Date[Date]=today()),Dim_Date[WeekNumber]))
VAR weekstart_ = if(
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])>1,MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ -1 && Dim_Date[WeekNumber] = 53 ), Dim_Date[Date]),
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
)
VAR weekend_ = if(
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])<7,MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ +1 && Dim_Date[WeekNumber] = 1 ), Dim_Date[Date]),
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
)
var total_records=calculate(sum(Fact_Timesheets[Planned Working Time]),filter(all(Dim_Date), Dim_Date[Date]>=weekstart_ && Dim_Date[Date]<=weekend_ && Dim_Date[WeekNumber]< max_week_),Fact_Timesheets[Time Category ID]<>12)
RETURN
total_records
 
New measures
weekstart =
VAR year_ = MAX ( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
VAR weekstart_ = calculate(if(
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])>1,
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ -1 && Dim_Date[WeekNumber] = 53 ), Dim_Date[Date]),
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
))
RETURN
weekstart_
*****************************************
weekend =
VAR year_ = MAX ( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
VAR weekend_ = calculate(if(
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])<7,MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ +1 && Dim_Date[WeekNumber] = 1 ), Dim_Date[Date]),
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
))
//if last week of the year spans across two years, get the first day of previous year
RETURN
weekend_
**************************************************
Total Hrs Planned Weekly =
VAR year_ = SELECTEDVALUE( Dim_Date[Year] )
var current_year_=year(today())
var max_week_= switch(true(),
year_ < current_year_, max(Dim_Date[WeekNumber]), maxx(filter(all(Dim_Date),Dim_Date[Date]=today()),Dim_Date[WeekNumber]))
var total_records=calculate(sum(Fact_Timesheets[Planned Working Time]),filter(all(Dim_Date), Dim_Date[Date]>=[weekstart] && Dim_Date[Date]<=[weekend] && Dim_Date[WeekNumber]< max_week_),Fact_Timesheets[Time Category ID]<>12)
total_records
 
 
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Jeanxyz ;

You could modify the measure as follows:

weekstart = 
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ = 
    IF (
        CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_
                    && Dim_Date[WeekNumber] = week_)) > 1,
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_ - 1
                    && Dim_Date[WeekNumber] = 53 )),
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_
                    && Dim_Date[WeekNumber] = week_) ))
RETURN  weekstart_
weekend = 
VAR year_ =
    CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
    CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
       CALCULATE( IF (
            CALCULATE (
                MAX ( Dim_Date[DayOfWeekNumber] ),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_
                        && Dim_Date[WeekNumber] = week_)) < 7,
            CALCULATE (
                MAX ( Dim_Date[Date] ),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_ + 1
                        && Dim_Date[WeekNumber] = 1 ) ),
            CALCULATE(MAX ( Dim_Date[Date]),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_
                        && Dim_Date[WeekNumber] = week_)  ) ))
RETURN  weekend_
Total Hrs Planned Weekly =
VAR year_ =
    SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
    YEAR ( TODAY () )
VAR max_week_ =
    SWITCH (
        TRUE (),
        year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
        MAXX (
            FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
            Dim_Date[WeekNumber]
        )
    )
VAR total_records =
    CALCULATE (
        SUM ( Fact_Timesheets[Planned Working Time] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
                && Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
                && Dim_Date[WeekNumber] < max_week_
        ),
        Fact_Timesheets[Time Category ID] <> 12
    )
RETURN
    total_records

The final output is shown below:

vyalanwumsft_0-1632208012298.png

Best Regards,
Community Support Team_ Yalan Wu
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

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @Jeanxyz ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @Jeanxyz ;

You could modify the measure as follows:

weekstart = 
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ = 
    IF (
        CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_
                    && Dim_Date[WeekNumber] = week_)) > 1,
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_ - 1
                    && Dim_Date[WeekNumber] = 53 )),
        CALCULATE (
            MIN ( Dim_Date[Date] ),
            FILTER (
                ALL ( Dim_Date ),
                Dim_Date[Year] = year_
                    && Dim_Date[WeekNumber] = week_) ))
RETURN  weekstart_
weekend = 
VAR year_ =
    CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
    CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
       CALCULATE( IF (
            CALCULATE (
                MAX ( Dim_Date[DayOfWeekNumber] ),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_
                        && Dim_Date[WeekNumber] = week_)) < 7,
            CALCULATE (
                MAX ( Dim_Date[Date] ),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_ + 1
                        && Dim_Date[WeekNumber] = 1 ) ),
            CALCULATE(MAX ( Dim_Date[Date]),
                FILTER (
                    ALL ( Dim_Date ),
                    Dim_Date[Year] = year_
                        && Dim_Date[WeekNumber] = week_)  ) ))
RETURN  weekend_
Total Hrs Planned Weekly =
VAR year_ =
    SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
    YEAR ( TODAY () )
VAR max_week_ =
    SWITCH (
        TRUE (),
        year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
        MAXX (
            FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
            Dim_Date[WeekNumber]
        )
    )
VAR total_records =
    CALCULATE (
        SUM ( Fact_Timesheets[Planned Working Time] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
                && Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
                && Dim_Date[WeekNumber] < max_week_
        ),
        Fact_Timesheets[Time Category ID] <> 12
    )
RETURN
    total_records

The final output is shown below:

vyalanwumsft_0-1632208012298.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jeanxyz
Post Prodigy
Post Prodigy

Here is a printscreen of the result, the new measure ignores week filter and resource ID (employee id). It sums up all hours of the select year month.

 

wrap formula.PNG

vanessafvg
Super User
Super User

when you say it doesn't work you haven't presented what the issue is ?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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