Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure from 2 Different Tables

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

General Comment
Need Help
Tips and Tricks

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Anonymous ,

    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.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi Anonymous ,

    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