Switch measure based on on ID if its present in another table

[Anonymous]

Hi All,

I would like to create a measure (not join tables or creating new tables) to list all the project numbers from table A and B and that if they are from A then take measure 1 else if its B then measure 2. PLUS if there are same project numbers (yellow colour above) in both A and B then take the measure from B (measure 2) 

Would be eternally grateful if someone could help me. T_____T

[parry2k]

@Anonymous it is not possible if you don't have a common table with all the ids. and doesn't make sense to achieve the result without having a common dimension.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi parry,

The reason why it needs to be a measure is because the two tables have different level of hierarchy. One is for e.g. L4 and the other L2 and they are separately connected to a dimension table. If u have an alternative solution using joins etc then let me knw 🙂

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I think building a new Product Num dimension table can make it easier for you to build a measure.

Calcualted table:

Product Num = 
SUMMARIZE(UNION( VALUES('Table A'[Product Num]),VALUES('Table B'[Project Num])),[Product Num])

Measure:

Measure = 
VAR _MEARSURE1 = 1
VAR _MEARSURE2 = 2
VAR _ProductNumA = VALUES('Table A'[Product Num])
VAR _ProductNumB = VALUES('Table B'[Project Num])
RETURN
IF(MAX('Product Num'[Product Num]) IN _ProductNumA&& MAX('Product Num'[Product Num]) IN _ProductNumB,_MEARSURE2,IF(MAX('Product Num'[Product Num]) IN _ProductNumB,_MEARSURE2,_MEARSURE1))

Result is as below.

Best Regards,
Rico Zhou

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