Forum Discussion
Modelling issue causing measure not properly working
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:
Hope its not too complicate
thanks a lot
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.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
12 Replies
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.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.
Hi,
i created the v2 file, based on the thoughts and its fine. The performance in the powerquery is maybe not the best for bigger sets, but its acceptable.
thanks
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
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:
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
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!Hi Hansolu,
As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?
Your understanding and patience will be appreciated.
Hi Hansolu,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
