Measure a KPI based on a date from related table

[Anonymous]

Hello, 

I have a question, how would one calculate the KPI of a Quality issue based on a date from another table that is related via a main table with active relationships. See the example below.

Many thanks!

[tex628]

Hi @Anonymous,

Which columns are the relationship on? 

Br,
J

[Anonymous]

Hi, so the Supplier Nr. would be the main ID for relationships.

Sorry forgot to mention

[tex628]

If the relationship is on supplier ID you will not be able to create any KPI that has a relation to periods. (In your image you are referencing months). 

I would create a calendar table and a table of distinct suppliers and connect them like this: 


Which should allow you to compare the defects with orders while also filtering on specific time periods. 

/ J

[Anonymous]

Hi J, Unfortunately it won't work - our data model has multiple connections with the table B (main table) therefore all other relationships won't be active.

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to use USERELATIONSHIP function?

Best Regards,

Dedmon Dai

[Anonymous · Accepted solution]

Hello, I solved the questions by myself.

Created a copy of Table A

Created a new relationship with Table C based on Supplier Nr.

Created 2 measures : 1 Sum Order QTY  2. Sum Defect QTY

Divided both measures 

Outcome: Order date now is used to see the overall performance based on total QTY Defect / monthly orders 

Thanks for looking up in the issue

[v-deddai1-msft]

Hi @Anonymous ,

Thank you for sharing the solution. Would you please accpet your reply as answer so that people with the same issue will find the solution fast.

Thank you for your support and understanding.

Best Regards,

Dedmon Dai

[Anonymous]

Hi J,

Will try and let you know if it worked. Thank you for the suggestion!

Best regards,

C

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try to create an inactive relationship between TABLE A and  TABLE B. Then use USERELATIONSHIP function. For more details, please refer to  https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Best Regards,

Dedmon Dai