Modelling issue causing measure not properly working

[Hansolu]

Hi,

i try to solve my issue since some time and close to give up. Would be nice if someone could help me.

Issue:

I do have a fact table with machine parameter for each cycle (date and time for each cycle and a cycleID). 

Second fact is a seperate list with failure parts, showing which cycleID was creating a bad part

third table is a batch table. After x parts we print a label and store the raw material used for this label. So actually the timekey of the batch table is showing: We use this batch for the period of the last label to now.

I want to calculate and I can do: 

How many parts did i produce every day (=is one cycle is one part)

How many bad parts per day and the percentage

What i can not do is the same measures but filtering down to the batch of.

e.g. a table with batches and the produced and bad parts for this batch only!

I uploaded a sample file here:

batch issues.pbix

Hope its not too complicate

thanks a lot

[v-sgandrathi]

Hi @Hansolu,

Thank you for the update. I’m pleased to hear that the measures are performing effectively and that you have established a stable solution using the FROM and TO logic with Power Query merge. This is a practical method, particularly for managing batch-level associations over time ranges.

Regarding Power Query merge performance with larger models, it is common to encounter trade-offs when working with extensive datasets and time-based logic. Since your refresh process runs in the background, it appears your current configuration meets your requirements.

For further efficiency, you might consider the following options:

Buffered Tables Before Merge: Applying Table.Buffer() prior to merging can sometimes improve join performance, especially with dynamic or external sources.

Reduce Merge Granularity: If feasible, pre-aggregating or grouping batch-to-cycle relationships before merging may help decrease processing time.

DAX Relationship Model (Advanced): If performance becomes a greater concern, implementing a calculated column with DAX and relationship techniques could be beneficial, depending on your model’s structure.

Below are the official documents you can have a look:
Optimize Power Query when expanding table columns - Power Query | Microsoft Learn
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
User-defined aggregations - Power BI | Microsoft Learn
Optimization guide for Power BI - Power BI | Microsoft Learn

Thank you again, and we hope you continue to find the Microsoft Fabric Community helpful!

[v-sgandrathi]

Hi @Hansolu,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  

I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you resolve your issue, if you need any further assistance, feel free to reach out.

Thank you for using Microsoft Community Forum.

[v-sgandrathi]

Hi @Hansolu,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Thank you.

[FBergamaschi]

Can you please attache a picture to explain better the problem, list the measures working and not working and expected outcome?? I am on the pbix but I cannot understand where to go

[Hansolu]

Hi,

thanks for looking into it.

I added some more explanation into the file. 

Basically, as soon as I drop in data from the rawmaterial table, the relationship is not clear and all number go to the level without rawmaterial. I somehow need to narrow down the realtionship so i can include my raw material.

Original data is much bigger, merge in PQ was a try but performance is really bad

New file:

batch issue_detailed.pbix

[FBergamaschi]

Thanks to you

Now the point is to give, to each row in the fact machine table, a raw material batch

If you explain in words how that association can be done, I think with a calculated column we can fix this

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Hansolu]

Hi, 

first, sorry for my late reply, was too busy and could not look into.

second, the conversation and the file from v-sgandrathim made me think another way.

Now, in the batch table I created a colum FROM and TO with Merge and index. Now I know when was the start and end of the batch.

Then I go to the create a new table , fact_machine_batches, and merge the corresponding batch info into this table. In the DM I connect them and now it looks good. 

I tested it also on my real life modell and the performance of the merge is not the best but its okay.

If you have a look and give me some suggestion to improve, that would help me also to improve my knowledge

thanks

batch issue_detailedV2.pbix

[FBergamaschi]

I am ready to help but please tell me which measure has bad perfomance

best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Hansolu]

Hi,

the performance of the measure if fine, just the merge function in the PQ,  to bring the batch info to the fact machine table is on a bigger modell a bit slow but acceptable as refresh is done in background

thanks for the input, helped me to find the solution