Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DGiyenko
Regular Visitor

Measure breaks relationship, best practice advice for historical data

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.

изображение_2022-11-20_142151891.png

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_IDBranchDateKEY
empl_1Branch_0101.11.202201.11.2022_empl_1
empl_2Branch_0201.11.202201.11.2022_empl_2
empl_3Branch_0301.11.202201.11.2022_empl_3
empl_4Branch_0401.11.202201.11.2022_empl_4
empl_1Branch_0102.11.202202.11.2022_empl_1
empl_2Branch_0102.11.202202.11.2022_empl_2
empl_3Branch_0302.11.202202.11.2022_empl_3
empl_4Branch_0402.11.202202.11.2022_empl_4

 

Sales table

DateEmployee_IDSales_AmountTypeKEY
01.11.2022empl_1100Type_101.11.2022_empl_1
01.11.2022empl_1200Type_201.11.2022_empl_1
01.11.2022empl_2150Type_101.11.2022_empl_2
01.11.2022empl_250Type_101.11.2022_empl_2
01.11.2022empl_2250Type_201.11.2022_empl_2
02.11.2022empl_1150Type_102.11.2022_empl_1
02.11.2022empl_1300Type_202.11.2022_empl_1
02.11.2022empl_2100Type_102.11.2022_empl_2
02.11.2022empl_2100Type_202.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.

DGiyenko_0-1668932853799.png

 

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")
DGiyenko_0-1668931691464.png

 

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())
DGiyenko_1-1668931799082.png

 

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!

 
1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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:

vtangjiemsft_0-1669102258273.png

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:

MicrosoftTeams-image (11).pngBest Regards,

Neeko Tang

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

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

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:

vtangjiemsft_0-1669102258273.png

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:

MicrosoftTeams-image (11).pngBest Regards,

Neeko Tang

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.