Measure breaks relationship, best practice advice for historical data

[DGiyenko]

Hello friends! I need some advice with a project I've been working on.

The basic premise is that I have a table with sales data, an employee table and a date table.

There are several stores(branches) that employees can work at and they rotate frequently. Management wants to see each store sales and performance, as well as individual employee performance. 

Dates are in european format "dd.mm.yyyy"

Employe table

Employee_ID	Branch	Date	KEY
empl_1	Branch_01	01.11.2022	01.11.2022_empl_1
empl_2	Branch_02	01.11.2022	01.11.2022_empl_2
empl_3	Branch_03	01.11.2022	01.11.2022_empl_3
empl_4	Branch_04	01.11.2022	01.11.2022_empl_4
empl_1	Branch_01	02.11.2022	02.11.2022_empl_1
empl_2	Branch_01	02.11.2022	02.11.2022_empl_2
empl_3	Branch_03	02.11.2022	02.11.2022_empl_3
empl_4	Branch_04	02.11.2022	02.11.2022_empl_4

Sales table

Date	Employee_ID	Sales_Amount	Type	KEY
01.11.2022	empl_1	100	Type_1	01.11.2022_empl_1
01.11.2022	empl_1	200	Type_2	01.11.2022_empl_1
01.11.2022	empl_2	150	Type_1	01.11.2022_empl_2
01.11.2022	empl_2	50	Type_1	01.11.2022_empl_2
01.11.2022	empl_2	250	Type_2	01.11.2022_empl_2
02.11.2022	empl_1	150	Type_1	02.11.2022_empl_1
02.11.2022	empl_1	300	Type_2	02.11.2022_empl_1
02.11.2022	empl_2	100	Type_1	02.11.2022_empl_2
02.11.2022	empl_2	100	Type_2	02.11.2022_empl_2

The way I made it work is by taking each employee's start and end date for a specific position and expanding that record for each day.

so for example if John was working as a Junior Sales Rep in Branch 1 from Jan 1-st till Jan 5-th, that record would be expanded into 5 rows with dates from Jan 1 to 5.

Then I create a KEY column by combining the employee ID with the DATE and do the same in the Sales table. So now I can Have a one-to-many relationship between the employee table and the sales table and link each emloyee with their sales for each day.

It works but I feel this is the worst possible way to do it, as it creates bloat and unnecessary data duplication.

What are some industry standarts or pest practices for this kind of setup? My intuition tells me that there should be a way to simply leverage the start and end dates without expanding the employee table for each day...

Any pointers or advice would be much appreciated!

The other problem I have is that I need to calculate a KPI measure for each employee.

Each employee is expected to sell at least 3 units for a total of 400 (for example).

When I create a simple measure it breaks the data model and relationships by duplicating rows for for every employee in the dataset and for each date in the date table, even though there is no data for these dates and employees.

KPI_WRONG =   IF(AND([SALES_AMT]>=400,[SALES_COUNT]>=3),"OK","NO")
I was able to find similar topics on this forum and was able to modify my measure to resolve the issue:

KPI =IF (

    MAX ( 'Sales'[Sales_Amount] ) <> BLANK(),

    IF(AND([SALES_AMT]>=400,[SALES_COUNT]>=3),"OK","NO"),BLANK())

Even though I was able to "solve" the issue, I am unable to understand WHY it happens and what exactly causes the rows to duplicate. What am I doing wrong here?

And even though I technically have a working measure, I am still left wondering if this is the way to do it? I feel like my data model is faulty and that is the reason for this strange behaviour. Is there a better way to shape the data?

I attached the example file with dummy data:

https://drive.google.com/file/d/1IcDY2bFMsL6omVmx8ZEiC79Z6b8ieG_O/view?usp=sharing

Thanks in advance!

[v-tangjie-msft · Accepted solution]

Hi @DGiyenko ,

For your problem, after testing, this cause is not because of your inter-table relationship, and there is no problem with one-to-many relationships. The presentation of your question does not meet your expectations because your measure "Measure = IF( SUM('Sales'[Sales_Amount])>=400 && COUNT('Sales'[Sales_Amount])>=3 , "OK","NO")"  returns data "NO" in this case, so it will show data that you do not want:

You need to add a BLANK() judgment to your measure to avoid this problem, you can try using this measure:

Measure = IF(SUM('Sales'[Sales_Amount])<>BLANK(), IF( SUM('Sales'[Sales_Amount])>=400 && COUNT('Sales'[Sales_Amount])>=3 , "OK","NO") ,BLANK())

The test results in my case are as follows:

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.