DAX: aggregated measure by another measure that should be calculated weekly

[Anonymous]

Hi Everyone, 

I need to calculate measure per time axis (yearly/monthly/weekly/daily) based on weekly values of another measure. 

SELECT COUNT(DISTINCT ESK), dd.MonthYear
FROM
(

SELECT SUM (TotalTime) as WeeklyTotalHours, WeekOfYear, Year, de.ESK
FROM core.FactTimePunch f
INNER JOIN core.DimDate dd
ON f.EntryDate = dd.DateBK
INNER JOIN core.DimEmployee de
ON de.ESK = f.ESK
GROUP BY WeekOfYear, Year, de.ESK
) as t
INNER JOIN core.DimDate dd
ON t.WeekOfYear = dd.WeekOfYear and t.Year = dd.Year
WHERE WeeklyTotalHours > 40
GROUP BY dd.MonthYear;



I wonder if you could help me with DAX measure with such logic. 
Thank you in advance

[v-jiascu-msft]

Hi @Anonymous,

Could you please post a sample data in text mode here? A SQL statement would be a good guide, but it wouldn't show up the source data and their relationship. (at least for me). 

Best Regards!

Dale

[Anonymous]

Hi @v-jiascu-msft, 

Here are relationships and sample of data from fact table: 

FactTimePunch M-1 DimEmployee (on ESK)
FactTimePunch M-1 DimDate (f.EntryDate = dd.DateBK)
FactTimePunch M-1 DimCompany (on CompanySK)

Sample of FactTimePunch data:

CompanySK	EntryDate	EntryDateID	ESK	TotalTime
9	5/30/2016 0:00	20160530	1027	8
9	5/30/2016 0:00	20160530	1028	8
9	5/30/2016 0:00	20160530	1029	8
9	5/30/2016 0:00	20160530	1030	8
9	5/30/2016 0:00	20160530	1031	8
9	5/30/2016 0:00	20160530	1032	8
9	5/30/2016 0:00	20160530	1033	8

[v-jiascu-msft]

Hi @Anonymous,

If you don't have "YearMonth" and "YearWeek" in your "DimDate" table, you could create them with the formula below.

YearWeek =
FORMAT ( [DateBK], "YYYYww" )

YearMonth =
FORMAT ( [DateBK], "YYYYmm" )

Finally we get the result. Maybe I make it complicated. I attached the file. 

FinalResult =
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                SUMMARIZE (
                    FactTimePunch,
                    'DimEmployee'[Esk],
                    'DimDate'[YearMonth],
                    'DimDate'[YearWeek],
                    "WeeklyHours", SUM ( 'FactTimePunch'[TotalTime] )
                ),
                [WeeklyHours] > 40
            ),
            "FinalESK", [Esk]
        )
    )
)

Only the FinalResult is right.

Best Regards!

Dale 

[Anonymous]

@v-jiascu-msft, thank you for your reply!

But I noticed that this formula works perfectly only on weekly grain. 

Look at the difference between DAX and SQL results:
 

I suppose that the difference takes place in weeks that relates to two months. 
For instance, we have 17 employes that overworked during Week5. 
This week relates to Jan2017 and Feb2017. Thus, these 17 employees should be included as in Jan2017 and Feb2017. 
But you can see on screenshots above that DAX formula does not return anything in Jan2017. 

Additionally, if I select daily grain for Ox axis, DAX formula returns nothing. But I need to include in each day employes that overworked during related to this day week.

Can you please review it?  

[v-jiascu-msft]

Hi @Anonymous,

How does your date table look like? Does it the same with it in the SQL DB? My formula has the problem of missing some employees. The reason is the week 5 will be split when month and week is applied at the same time. Then the total work hours of the week 5 couldn't bigger than 40 hours. But the SQL statement may return more than the actual number. Could you please verify it? Because the SQL statement groups the value first by week number, then by month number. There may be duplicates.

I will keep working on it.

Best Regard!

Dale

[Anonymous]

Hi @v-jiascu-msft, 

I added  'DimDate'[YearMonth], 'DimDate'[YearWeek] columns in PowerBI Model as you suggested. 

Also I tried to modify SUMMARIZE statement and excluded YearMonth column:

SUMMARIZE (
                    FactTimePunch,
                    'DimEmployee'[Esk],
                    'DimDate'[YearWeek],
                    "WeeklyHours", SUM ( 'FactTimePunch'[TotalTime] )
                ), 

 But it did not help

[v-jiascu-msft]

Hi @Anonymous,

The formula I posted isn't the solution. I still work on it. Did you verify the results of SQL statement? Are they correct?

Best Regards!

Dale

[Anonymous]

Hi @v-jiascu-msft, 

Do you have any new thoughts on it? 

I did some progress with using calculated table with cross join but can not finally finish it: 

Calculated Table: 

Table of Cross ESK and Days =
var TableOfEsk =
SELECTCOLUMNS(
FILTER(
SUMMARIZE(vFactTimePunch,'vDimEmployee'[ESK], 'vDimDate'[YearWeek],"WeeklyHours", SUM('vFactTimePunch'[TotalTime])),
[WeeklyHours]>40
),
"FinalESK", [ESK], "T1YearWeek", [YearWeek]
)
RETURN
FILTER(
CROSSJOIN(TableOfEsk, vDimDate),
[T1YearWeek] = [YearWeek]
)

This new table has relationships with old dimensions.

Measure based on new calculated table: 

FinalResult Cross =
COUNTROWS(
DISTINCT(
'Table of Cross ESK and Days'[FinalESK]
))

It returns correct values. 

Issue is that value 40 will not be static.
It will be also measure. 
Unfortuanetlly, calculated table is not recalculates when some input values changes. 
It means that I can not use calculated table. 

I tried to put this statement of cross joined table in measure. But in this case this measure does not ignore monthly filter contex even when I add ALL() to ignore filter contex. 

Any thoughts? 

 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

The key point is the context. When we evaluate the weekly values, we have to ignore month. While we evaluate the result, we have to consider the month. This troubles me a long time. I made some changes of your formula. Then you can apply a dynamic value, not just 40.

Table of Cross ESK and Days =
VAR TableOfEsk =
    SELECTCOLUMNS (
        SUMMARIZE (
            vFactTimePunch,
            'vDimEmployee'[ESK],
            'vDimDate'[YearWeek],
            "WeeklyHours", SUM ( 'vFactTimePunch'[TotalTime] )
        ),
        "FinalESK", [ESK],
        "T1YearWeek", [YearWeek],
        "WeeklyHours", [WeeklyHours]
    )
RETURN
    DISTINCT (
        FILTER (
            CROSSJOIN (
                SELECTCOLUMNS ( vDimDate, "YM", vDimDate[YearMonth], "YW", vDimDate[YearWeek] ),
                TableOfEsk
            ),
            [T1YearWeek] = [yw]
        )
    )

1. Don't filter value 40. Keep all values, then you can filter it dynamically.

2. Only two columns of vDimDate applied. 

3. Keep distinct values.

4. This table will updated automatically.

Best Regards!

Dale

[v-jiascu-msft]

Hi @Anonymous,

It seems I work it out as a measure. Please try it and share the result with us.

FinalResult2 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            vDimDate,
            vDimDate[YearMonth],
            vDimDate[YearWeek],
            "value", SUMX (
                CALCULATETABLE (
                    SUMMARIZE (
                        vFactTimePunch,
                        DimCompany[CompanySK],
                        vDimDate[YearWeek],
                        "weeklyhour", SUM ( vFactTimePunch[TotalTime] )
                    ),
                    ALLEXCEPT ( vDimDate, vDimDate[YearWeek] )
                ),
                [weeklyhour]
            )
        ),
        [value] > 40
    )
)

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft, 

Great catch is do not filter crossjoined calculated table to keep dynamicly change threeshold that now equals 40!
Thank you a lot, it works for me.

With regard to last comment with measure, unfortunately, it does not work correctly: 

 
Left is correct result with calculated table, right is incorrect result with measure from last comment. 
I am not sure about SUMX in this measure. It seems that we should not use SUMX here.

Thanks, 

[v-jiascu-msft]

Hi @Anonymous,

As we can see from the context of "sumx", there is only one value to sum up. The final try with this formula.

FinalResult2 =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            CROSSJOIN (
                VALUES ( vDimEmployee[Esk] ),
                SELECTCOLUMNS ( vDimDate, "YM", vDimDate[YearMonth], "YW", vDimDate[YearWeek] )
            ),
            [Esk],
            [YM],
            [YW],
            "value", SUMX (
                CALCULATETABLE (
                    SUMMARIZE (
                        vFactTimePunch,
                        DimCompany[CompanySK],
                        vDimDate[YearWeek],
                        "weeklyhour", SUM ( vFactTimePunch[TotalTime] )
                    ),
                    ALLEXCEPT ( vDimDate, vDimDate[YearWeek] )
                ),
                [weeklyhour]
            )
        ),
        [value] > 40
    )
)

Remember to share the result and mark the proper answer as solution please.

Best Regards!

Dale

[Anonymous]

@v-jiascu-msft, 

Yes, SQL statement returns correct result