Countrows incorrect

[Anonymous]

Hi Community,

I have a measure to calculate total leaves taken which excludes weekends & public holidays. Countrows is not yeilding correct result.

For example in following,  In Dec, emp 12 took 21 leaves not 13

My sample PBI file:  https://1drv.ms/u/s!Ag919_pO_UKrgSjIVaFeSDgQFcn1?e=lCvuJq

Your help is very much appreciated.

Thanks

[BarnabasToth]

Hi there,

A little tweak on the model, creating a table like this (better to be created at source though):

Emp Leave RelevantDays = -- SELECTCOLUMNS not necessary
SELECTCOLUMNS (
    GENERATE (
        'Emp Leave',
        DATESBETWEEN( datedim[Date], 'Emp Leave'[date_start], 'Emp Leave'[date_end] )
    ),
    "emp_code", 'Emp Leave'[emp_code],
    "RelevantDates", 'datedim'[Date]
)

 Can make the DAX code easier:

Solution 2 = 
CALCULATE (
    COUNTROWS ( 'Emp Leave RelevantDays' ),
    datedim[working days] = 1
)

See file here. 

Regards

[amitchandak · Accepted solution]

@Anonymous , Try like

Total Leaves = 
CALCULATE( SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Emp Leave', 'Emp Leave'[emp_code], 'Emp Leave'[date_start], 'Emp Leave'[date_end] ),
        "diff",
            COUNTROWS (
                FILTER (
                    Datedim,
                    Datedim[Date] >=  ( 'Emp Leave'[date_start] )
                        && Datedim[Date] <=  ( 'Emp Leave'[date_end] ) && datedim[working days]=1
                )
            )
    ),
    [diff]
), CROSSFILTER('Emp Leave'[date_start],datedim[Date],None))