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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.