Forum Discussion

Hansolu's avatar
Hansolu
Helper II
1 year ago
Solved

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 ...
Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

  • Hansolu's avatar
    Hansolu
    1 year ago

    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

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

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's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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.

     

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      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!