Total workingdays by subgroup

[Ronald123]

Hello,

Can anyone help me with a measure for the total workingsdays by subgroup.

In the middle matrix the measure workingsdays shows the right number of working by contno.

In the fist matrix i need the totals of all workingsdays from the contract in the subgroup WCKLEP.

 

[Eric_Zhang]

@Ronald123

You need to create a measure as below. See more details in the attached pbix file.

workingdaysTotal = 
VAR summrizedTBL =
    SUMMARIZE (
        History,
        History[CONTNO],
        History[SDATE],
        History[EDATE],
        "workingdays", [Workingdays]
    )
RETURN
    IF (
        ISFILTERED ( History[CONTNO] ),
        [Workingdays],
        SUMX ( summrizedTBL, [workingdays] )
    )

[Ronald123]

@Eric_Zhang

Hello Eric,

Thank you for the measure, the calculation in perfect.

I have only a performance problem, i have a sql connection in the direct query mode, it's verry slow.

The table history have 400K lines, and this is with a filter of the years 2016 / 2017.

The date / history table have not a relationship at this moment.

Is there someting to do for a faster report?

Beste Regards,

Ronald

[Eric_Zhang · Accepted solution]

---

@Ronald123 wrote:

@Eric_Zhang

Hello Eric,

Thank you for the measure, the calculation in perfect.

I have only a performance problem, i have a sql connection in the direct query mode, it's verry slow.

The table history have 400K lines, and this is with a filter of the years 2016 / 2017.

The date / history table have not a relationship at this moment.

Is there someting to do for a faster report?

Beste Regards,

Ronald

---

@Ronald123

Using complicated measure in DirectQuery mode affects the performance, in your case, I'd suggest you generate workingdays in the table2 of your original post in SQL query.