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,

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