Forum Discussion

kl8818's avatar
kl8818
Frequent Visitor
2 years ago
Solved

Change Formula

Could someone help me update this formula: Count employee with 2 or more login: = VAR _t =     ADDCOLUMNS (         VALUES ( 'User Logins'[Employee ID] ),         "@logincount", CALCULAT...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Count Expected result measure: = 
VAR _today =
    TODAY ()
VAR _lastmonthenddate =
    EOMONTH ( _today, -1 )
VAR _twomonthsbeforestartdate =
    EOMONTH ( _today, -3 ) + 1
VAR _monthtable =
    FILTER (
        ADDCOLUMNS (
            'Users Logins',
            "@year-month", EOMONTH ( 'Users Logins'[Logged In], 0 )
        ),
        [@year-month] >= _twomonthsbeforestartdate
            && [@year-month] <= _lastmonthenddate
    )
VAR _summarytable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( _monthtable, [@year-month], 'Users Logins'[Employee ID] ),
            "@logincount", CALCULATE ( COUNTROWS ( 'Users Logins' ) )
        ),
        [@logincount] >= 1
    )
VAR _idlist =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                _summarytable,
                "@condition",
                    COUNTROWS (
                        FILTER (
                            _summarytable,
                            'Users Logins'[Employee ID] = EARLIER ( 'Users Logins'[Employee ID] )
                        )
                    )
            ),
            [@condition] >= 2
        ),
        'Users Logins'[Employee ID]
    )
RETURN
    COUNTROWS ( FILTER ( _monthtable, 'Users Logins'[Employee ID] IN _idlist ) )
kl8818's avatar
kl8818
Frequent Visitor
2 years ago

Thanks for sharing this! Is there a way to have that same visual and it show a running count instead of the employee ID? Is that something to adjust in the formula or in the visual?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Count Expected result measure: = 
VAR _today =
    TODAY ()
VAR _lastmonthenddate =
    EOMONTH ( _today, -1 )
VAR _twomonthsbeforestartdate =
    EOMONTH ( _today, -3 ) + 1
VAR _monthtable =
    FILTER (
        ADDCOLUMNS (
            'Users Logins',
            "@year-month", EOMONTH ( 'Users Logins'[Logged In], 0 )
        ),
        [@year-month] >= _twomonthsbeforestartdate
            && [@year-month] <= _lastmonthenddate
    )
VAR _summarytable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( _monthtable, [@year-month], 'Users Logins'[Employee ID] ),
            "@logincount", CALCULATE ( COUNTROWS ( 'Users Logins' ) )
        ),
        [@logincount] >= 1
    )
VAR _idlist =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                _summarytable,
                "@condition",
                    COUNTROWS (
                        FILTER (
                            _summarytable,
                            'Users Logins'[Employee ID] = EARLIER ( 'Users Logins'[Employee ID] )
                        )
                    )
            ),
            [@condition] >= 2
        ),
        'Users Logins'[Employee ID]
    )
RETURN
    COUNTROWS ( FILTER ( _monthtable, 'Users Logins'[Employee ID] IN _idlist ) )