Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Effective Dated User Table with manager Information

Hi all,

 

I have a requirement to display the count of a measure per employee and per Manager in a power BI dashboard. The manager information can be changed at anytime and the employee can be reporting to a diffrent manager, in this case we dont need to loose the historical data that belonged to the previous manager when the employee was reporting to him. So we have introduced an effective end date concept. The tables looks like this.

 

User :

 

UserManagerEffective EndDate
AB1/2/2022
AC 
DB 


Fact Table :

 

UserDateCount
A1/1/202210
A1/2/202220
A1/7/202230
A1/8/202210
D1/1/202210
D1/4/202220



 

Apart from this we have a date table for date slicer.

 

We need to Build a matrix like below.

 

Date Range Selected : 01/01/2022 to 01/10/2022 in Slicer

 

UserManagerCount
AB30
AC40
DB30


Would you be able to help me to build a measure to acheive this .?

 

Thanks.

 

Regards,

Aju P Sebastian

 

Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    This solution requires a slight change to the User table. If you restructure it to use Start Date and End Date, it simplifies the DAX:

     

     

    Create a bridge table using DAX (calculated table):

     

    DistinctUser = DISTINCT ( User[User] )

     

    Create relationships as shown below. If you prefer not to use a bidirectional relationship between User and DistinctUser, you can use USERELATIONSHIP in the measure (and specify bidirectional).

     

     

    Use either of the measures below (you can try both to see which is more performant):

     

    Count CALCULATE = 
VAR vStartDate =
    MAX ( User[Start Date] )
VAR vEndDateCol =
    MAX ( User[End Date] )
VAR vEndDate =
    IF ( ISBLANK ( vEndDateCol ), DATE ( 9999, 12, 31 ), vEndDateCol )
VAR vAmount =
    CALCULATE ( SUM ( FactTable[Count] ),
        FactTable[Date] >= vStartDate,
        FactTable[Date] <= vEndDate
        )
// calculate total
VAR vResult =
    IF ( HASONEVALUE ( User[User] ), vAmount, SUM ( FactTable[Count] ) )
RETURN
    vResult

     

    Count SUMX = 
VAR vStartDate =
    MAX ( User[Start Date] )
VAR vEndDateCol =
    MAX ( User[End Date] )
VAR vEndDate =
    IF ( ISBLANK ( vEndDateCol ), DATE ( 9999, 12, 31 ), vEndDateCol )
VAR vAmount =
    SUMX (
        FactTable,
        IF (
            FactTable[Date] >= vStartDate
                && FactTable[Date] <= vEndDate,
            FactTable[Count]
        )
    )
// calculate total
VAR vResult =
    IF ( HASONEVALUE ( User[User] ), vAmount, SUM ( FactTable[Count] ) )
RETURN
    vResult

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    Anonymous,

     

    This solution requires a slight change to the User table. If you restructure it to use Start Date and End Date, it simplifies the DAX:

     

     

    Create a bridge table using DAX (calculated table):

     

    DistinctUser = DISTINCT ( User[User] )

     

    Create relationships as shown below. If you prefer not to use a bidirectional relationship between User and DistinctUser, you can use USERELATIONSHIP in the measure (and specify bidirectional).

     

     

    Use either of the measures below (you can try both to see which is more performant):

     

    Count CALCULATE = 
VAR vStartDate =
    MAX ( User[Start Date] )
VAR vEndDateCol =
    MAX ( User[End Date] )
VAR vEndDate =
    IF ( ISBLANK ( vEndDateCol ), DATE ( 9999, 12, 31 ), vEndDateCol )
VAR vAmount =
    CALCULATE ( SUM ( FactTable[Count] ),
        FactTable[Date] >= vStartDate,
        FactTable[Date] <= vEndDate
        )
// calculate total
VAR vResult =
    IF ( HASONEVALUE ( User[User] ), vAmount, SUM ( FactTable[Count] ) )
RETURN
    vResult

     

    Count SUMX = 
VAR vStartDate =
    MAX ( User[Start Date] )
VAR vEndDateCol =
    MAX ( User[End Date] )
VAR vEndDate =
    IF ( ISBLANK ( vEndDateCol ), DATE ( 9999, 12, 31 ), vEndDateCol )
VAR vAmount =
    SUMX (
        FactTable,
        IF (
            FactTable[Date] >= vStartDate
                && FactTable[Date] <= vEndDate,
            FactTable[Count]
        )
    )
// calculate total
VAR vResult =
    IF ( HASONEVALUE ( User[User] ), vAmount, SUM ( FactTable[Count] ) )
RETURN
    vResult