Measure from 2 Different Tables

[vanquisher11]

Hi all,

I wanted to create summary table to show the scores of each objective.

So at the moment, I have 3 tables:

Dimension table [Objectiveskey, Measure Name, Description, Department]

Fact table 1[Objectivekey, Datekey, score, target,...]

Fact table 2[Ojectivekey, Datekey, condition, confidence rating, ...]

So I was hoping to create a visual that shows,

Measure Name, Score/Condition, Target

But not sure what's the best approach and how to deal with this?

Tried merging the tables and creating a conditional column, but so far doesn't work.

Will a nested if measure suffice? Not sure how what commands is best though.

Many thanks

Matt

[v-kkf-msft]

Hi @vanquisher11 ,

Did I answer your question? If you can get the correct result, please accept as solution so that users with the same problem can find it quickly. If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @vanquisher11 ,

You may create two measures:

_condition = 
CALCULATE(
    SUM('Fact table 2'[condition]),
    FILTER(
        ALL('Fact table 2'),
        'Fact table 2'[Objectivekey] = MAX('Dimension table'[Objectiveskey])
        && 'Fact table 2'[Datekey] = MAX('Fact table 1'[Datekey])
    )
)

score/condition = DIVIDE( MAX('Fact table 1'[score]), [_condition] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Measures can work with data from different tables even if the tables are not connected in the data model. Please provide sample data in usable format (not as a picture) and show the expected outcome.