Countrows/calculate/sum rows in DAX

Question

I am fairly new with Power BI and DAX and I'm stuck. I'll try to explain the current situation and what I want to become my output. I've tried a lot of meaures with distinctcount, calculate, you name it, I did it. But can't find the right solution.

We've got 4 columns: Date, Employee_ID, Sick, %FTE. Every row records if an employee was sick on that date. Blank is not sick and Y = sick.

I would like to create a measure where it counts the %FTE just once when an employee is sick in a particular week, month or year.

So the output of January should be 2,13 (0,8 + 0,33 + 1) and in February 1,8 (0,8 + 1).

enter image description here

smpa01 smpa01 · Accepted Answer · 2021-07-14T20:37:35

You would need two additional columns in the dataset as following

Once you have that, you can use the following measures to reach the goal

Measure8 =
VAR _1 =
    IF (
        MAX ( 'fact'[sick] ) <> BLANK (),
        RANKX (
            FILTER (
                ALL ( 'fact' ),
                'fact'[emp_id] = MAX ( 'fact'[emp_id] )
                    && 'fact'[Year] = MAX ( 'fact'[Year] )
                    && 'fact'[Month] = MAX ( 'fact'[Month] )
                    && 'fact'[sick] = "Y"
            ),
            CALCULATE ( MAX ( 'fact'[date] ) ),
            ,
            ASC,
            DENSE
        )
    )
VAR _2 =
    IF ( _1 = 1, IF ( MAX ( 'fact'[sick] ) = "y", MAX ( 'fact'[%FTE] ) ) )
RETURN
    _2

Measure9 =
IF (
    HASONEVALUE ( 'date'[date] ),
    [Measure8],
    VAR _1 =
        MAXX (
            GROUPBY (
                ADDCOLUMNS ( 'fact', "val", [Measure8] ),
                [Year],
                [Month],
                "total", SUMX ( CURRENTGROUP (), [val] )
            ),
            [total]
        )
    VAR _2 =
        MAXX (
            GROUPBY (
                ADDCOLUMNS ( 'fact', "val", [Measure8] ),
                [Year],
                "total", SUMX ( CURRENTGROUP (), [val] )
            ),
            [total]
        )
    VAR _3 =
        IF ( ISINSCOPE ( 'fact'[Year] ), _2, _1 )
    RETURN
        _3
)

Also, for any future posts please provide the sample data and expected output as markdown tables How To

Countrows/calculate/sum rows in DAX

1 Answers