General help with how to handle changing employee positions and changing departments

[ShaelynFrench]

Hi,

I'm just looking for some general advice on how to handle reporting on employees when their positions and departments are constantly changing. I currently have a DAX measure that looks up the employee's position based on the month/date chosen in a slicer, but I am unable to use the Measure as a slicer to view one group of positions at a time. Same for department. If the department is dynamic in a Measure then I am unable to use a slicer for department. I would also like to subtotal reports by position which is also not allowed since Measures cannot be used as Rows in the Matrix visual. Surely others have had this issue and I've scoured google and I have not found any feasible solutions.

[DataInsights]

@ShaelynFrench,

Would you be able to provide a mock-up with sample data (paste as table) and the expected result? Does your data have an Effective Date column, or Date From/Date To columns?

[ShaelynFrench]

Hi,

Thank you for responding. Yes, I have an effective date for the changes. I created a sample file. Here: https://drive.google.com/file/d/105S9usiQ1uQgvItK6_4JTrVOF0NqRK_t/view?usp=share_link 

You can see that I have the [ProfType] measure working exactly as intended, but I also want to be able to use that measure in a slicer and subtotal the matrix by that measure. Since neither of those things are possible in Power BI, I'm wondering if there is a better way to structure the data to avoid having to use a measure for this data or another work around or something I'm not thinking of?

[Ashish_Mathur]

Hi,

I think the approach should be as follows:

1. In the dbo_ProfType table, create another column (Effective date from in the Query Editor).  So for Prof167 in row 1, this column will show 31/1/2021 and in row 7, it will show today's date.
2. In the Query Editor, for each row, we will then create one row for each month
3. We will then create a calculated column formula in the dbo_transactions table to fetch the Professional type column from the dbo_ProfType table based on 2 common columns (Professionals and Date).  This column can now be dragged to the slicer.

[DataInsights]

@ShaelynFrench,

Try this calculated column in dbo_Transactions:

ProfType = 
VAR vEffDate =
    MAXX (
        FILTER (
            RELATEDTABLE ( dbo_ProfType ),
            dbo_ProfType[Effective Date] <= dbo_Transactions[TransDate]
        ),
        dbo_ProfType[Effective Date]
    )
VAR vResult =
    MAXX (
        FILTER (
            RELATEDTABLE ( dbo_ProfType ),
            dbo_ProfType[Effective Date] = vEffDate
        ),
        dbo_ProfType[Professional Type]
    )
RETURN
    vResult

It retrieves the Professional Type in effect on the TransDate. Create a slicer using this calculated column.

[ShaelynFrench]

@DataInsights Hi, thank you again for your response. I had considered using a calculated column in the Transactions table, so I will give it a shot, but I have two concerns. First is regarding performance since Transactions table in my actual model is at least a million rows and growing every day. And second, I'm concerned this solution might make professionals show up on the report twice (once in each category if they have hours from a prior year that haven't been billed yet).

[DataInsights]

@ShaelynFrench,

Is the requirement to show all of a professional's hours in the category that corresponds to the date slicer, as opposed to the category that corresponds to the transaction date?

[ShaelynFrench]

@DataInsights Yes, exactly, it should be as of the slicer date, not necessarily as of the Transaction date. Thanks!