Change values to Blank in measure if date is before specific date.

[Kamill11]

Hi,

I have a measure that shows if the employee was at work or  absent. I show it on the matrix and use the calendar which is from 1/1/2020. I have a date assigned to each employee when he started work, the problem is the measure shows values for each date, there are values in 2020 but the employee started work in 2021. can values before the start date be changed to blank?

Employee 1 started work on 1/03/2022, i want shows all values before as Blank.

Test Results = CALCULATE(
 IF(AND(COUNT([absence])=0, COUNT([terminal])=0),"3",

 IF(AND(COUNT([absence)>0, COUNT([terminal])=0),"",

 IF(AND(COUNT([absence])=0, AND(COUNT([terminal])>0, ISEVEN(COUNT([terminal])))),"",
 
 IF(AND(COUNT(absence])=0, AND(COUNT([terminal])>0, NOT ISEVEN(COUNT([terminal])))),"2",

 IF(AND(COUNT([absence])>0, COUNT([terminal])>0),"1", "uknown")

)))),

DATESBETWEEN('Date'[fulldate],LASTDATE(employee[date_started_with_company]),TODAY()

))

[Kamill11]

Thank you works well for employees,

is there any chance to show SUM of measure but not for each employee,but for example for departments ( SUM of all anomalies of all employess assigned to department). Deparment is included in employee table.
Now measure is not showing correct values and i have no idea where it got results from

3 employees are assigned to department 3. sum of their annomalies is 29, i want shown this in measure. need I to build another measure or can i modifiy this? Please dont give me ready solution, direct me to think well. This way i can learn more, thank you 🙂

data:
https://1drv.ms/u/s!Aq8vAodERtBchicfGV5KptFBY4ic?e=NOas1S

[Kamill11]

Hi @v-yiruan-msft 
thank you for you reply. This solution switch all values to blank, i want switch only values before start_with_company date. Im not sure if that is posible to do. 

For example user 955 started 9/03/2022, i want show matrix for him like this: 

'work'

employee_iddate_and_timeabsence_idterminal_id

955	11/03/2022 00:00		1
955	10/03/2022 00:00		1
955	09/03/2022 00:00		1
955	08/03/2022 00:00		1
954	07/03/2022 00:00
955	07/03/2022 00:00

'employee'

employee_id	date_started_with_company
955	09/03/2022 00:00

How can i share pbix or xlsx files? Im getting error "The file type (.xlsx) is not supported."

[v-yiruan-msft]

Hi @Kamill11 ,

You can refer the following thread to upload your file in the community. It is better if you can share a simplified pbix file(exclude sensitive data), then I get more info(data, table relationship, visual settting etc) from your shared pbix file. Thank you.

How to upload PBI in Community

Best Regards

[Kamill11]

Thank you @v-yiruan-msft , hope this one is corret. You can check results on employee_id 955. Start date is 9/03/22 and i want show blank on values before this date. and calculation for him starts from his start date.

https://1drv.ms/u/s!Aq8vAodERtBchicfGV5KptFBY4ic?e=QTetmK

[v-yiruan-msft]

Hi @Kamill11 ，

I updated your sample pbix file(see attachment), please check whether it can get your desired result. Please update the formula of measure [Test Result2] as below:

Test Result2 = 
VAR _selemp =
    SELECTEDVALUE ( 'employee'[employee_id] )
VAR _startdate =
    CALCULATE (
        MAX ( 'employee'[date_started_with_company] ),
        FILTER ( 'employee', 'employee'[employee_id] = _selemp )
    )
VAR _anodate =
    SELECTEDVALUE ( 'Twork'[date_and_time] )
RETURN
    IF (
        _anodate < _startdate
            || AND ( COUNT ( 'Twork'[absence_id] ) > 0, COUNT ( 'Twork'[terminal_id] ) = 0 )
            || AND (
                COUNT ( 'Twork'[absence_id] ) = 0,
                AND (
                    COUNT ( 'Twork'[terminal_id] ) > 0,
                    ISEVEN ( COUNT ( 'Twork'[terminal_id] ) )
                )
            ),
        "",
        IF (
            AND ( COUNT ( 'Twork'[absence_id] ) > 0, COUNT ( 'Twork'[terminal_id] ) > 0 ),
            "1",
            IF (
                AND (
                    COUNT ( 'Twork'[absence_id] ) = 0,
                    AND (
                        COUNT ( 'Twork'[terminal_id] ) > 0,
                        NOT ( ISEVEN ( COUNT ( 'Twork'[terminal_id] ) ) )
                    )
                ),
                "2",
                IF (
                    AND ( COUNT ( 'Twork'[absence_id] ) = 0, COUNT ( 'Twork'[terminal_id] ) = 0 ),
                    "3",
                    "uknown"
                )
            )
        )
    )

Any comment or problem, please feel free to let me know.

Best Regards

[Kamill11]

Works great! I rebuild your measure a little and now it is more visiable  🙂 

Test Result2 = 
VAR _selemp =
    SELECTEDVALUE ( 'employee'[employee_id] )
VAR _startdate =
    CALCULATE (
        MAX ( 'employee'[date_started_with_company] ),
        FILTER ( 'employee', 'employee'[employee_id] = _selemp )
    )
VAR _anodate =
    SELECTEDVALUE ( 'Twork'[date_and_time] )

    VAR _first =
                _anodate < _startdate
            || AND ( COUNT ( 'Twork'[absence_id] ) > 0, COUNT ( 'Twork'[terminal_id] ) = 0 )
            || AND (
                COUNT ( 'Twork'[absence_id] ) = 0,
                AND (
                    COUNT ( 'Twork'[terminal_id] ) > 0,
                    ISEVEN ( COUNT ( 'Twork'[terminal_id] ) )
                )
            )


    VAR _sec =  
            AND ( COUNT ( 'Twork'[absence_id] ) > 0, COUNT ( 'Twork'[terminal_id] ) > 0 )

    VAR _third =  AND (
                    COUNT ( 'Twork'[absence_id] ) = 0,
                    AND (
                        COUNT ( 'Twork'[terminal_id] ) > 0,
                        NOT ( ISEVEN ( COUNT ( 'Twork'[terminal_id] ) ) )
                    ))


    VAR _fourth = AND ( COUNT ( 'Twork'[absence_id] ) = 0, COUNT ( 'Twork'[terminal_id] ) = 0 )


RETURN

SWITCH(
    TRUE(),
    _first, BLANK (),
    _sec,1,
    _third,2,
    _fourth,3,
    BLANK()
)
       
           

But, found another problem 🙂 Is it posible to count results of 1st measue? Like on attached screenshot. I need to show how many "anomalies" each employee had for dates in filters.  

PS. SUM = 5 in third line, my bad 🙂 

[v-yiruan-msft · Accepted solution]

Hi @Kamill11 ,

Please create another measure as below to replace the original measure [Test Result2] on the matrix:

Measure = 
IF (
    ISINSCOPE ( 'twork'[date_and_time] ),
    [Test Result2],
    SUMX (
        FILTER ( VALUES ( 'twork'[date_and_time] ), [Test Result2] = 1 ),
        [Test Result2]
    )
)

Best Regards

[Kamill11]

Works great, thank you @v-yiruan-msft  . I need to create a litle update to this so....

Is there any change to shows by this measure SUM of anomalies, but assigned to diferent column od 'employee' tab? for excample, evey employee is assigned to departent, i want show number of anomalies for department (sum of all anomalies of all employees asigned to department). I attach example file, but please dont give ready solution, just direct me to good thinking. This way i can learn more 🙂 Thank you. 
Example: 
3 employees are assignet to department 3, sum of their anomalies is 29, measure shows 9. 

https://1drv.ms/u/s!Aq8vAodERtBchicfGV5KptFBY4ic?e=wW4tqM

[v-yiruan-msft]

Hi @Kamill11 ,

Please update the formula of the measure [Test Results] as below and check whether it can get your desired result:

Test Results =
VAR _selemp =
    SELECTEDVALUE ( 'employee'[Full User Name] )
VAR _startdate =
    CALCULATE (
        MAX ( 'employee'[date_started_with_company] ),
        FILTER ( 'employee', 'employee'[Full User Name] = _selemp )
    )
VAR _seldate =
    SELECTEDVALUE ( 'Date'[fulldate] )
RETURN
    SWITCH (
        _seldate < _startdate
            || _seldate > TODAY ()
            || AND ( COUNT ( [absence] ) > 0, COUNT ( [terminal] ) = 0 )
            || AND (
                COUNT ( [absence] ) = 0,
                AND ( COUNT ( [terminal] ) > 0, ISEVEN ( COUNT ( [terminal] ) ) )
            ),
        "",
            AND ( COUNT ( [absence] ) > 0, COUNT ( [terminal] ) > 0 ),
        "1",
            AND (
                COUNT ( [absence] ) = 0,
                AND ( COUNT ( [terminal] ) > 0, NOT ( ISEVEN ( COUNT ( [terminal] ) ) ) )
            ),
        "2",
            AND ( COUNT ( [absence] ) = 0, COUNT ( [terminal] ) = 0 ),
        "3", "uknown"
    )

If the above one can't help you get the correct result, please provide some sample data(exclude sensitive data) with Text format and your actual expected result with backend logic and special examples. Thank you.

Best Regards