Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I would like to create a column or measure in table 2 which checks if a combination occurs in table 1. So for example product=a and customer=2 does not occur in table 1 so the result in table 2 for this combination is 'Not OK'.
The tables are related on product.
Thanks already!
Table1
Product | Customer |
a | 1 |
b | 2 |
c | 3 |
Table2
Product | Customer | result |
a | 1 | OK |
a | 2 | NOT OK |
b | 2 | OK |
c | 3 | OK |
c | 2 | NOT OK |
Solved! Go to Solution.
@Nijlal01 , you may tweak the measure this way
Result =
IF (
CALCULATE (
ISEMPTY ( 'Product Filtered GlobalID' ),
TREATAS (
SUMMARIZE ( ItemsMapping, ItemsMapping[Code], ItemsMapping[ID] ),
'Product Filtered GlobalID'[Product.ProductCode],
'Product Filtered GlobalID'[Product.ProductGlobalID]
)
),
"NOT Ok",
"OK"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Nijlal01 ,
Create a Calculated Column in Table 2.
Final Column = IF (Table2[Customer] = LOOKUPVALUE(Table1[Customer],Table1[Product],Table2[Product]) , "OK" , "Not Ok")
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks! It almost works. In the real example however both tables do have more columns. It seems that therefore this option does not work completely. However only the 2 columns (like in the example) have to be compared. Any suggestion how to solve this?
@Nijlal01 , you may tweak the measure this way
Result =
IF (
CALCULATE (
ISEMPTY ( 'Product Filtered GlobalID' ),
TREATAS (
SUMMARIZE ( ItemsMapping, ItemsMapping[Code], ItemsMapping[ID] ),
'Product Filtered GlobalID'[Product.ProductCode],
'Product Filtered GlobalID'[Product.ProductGlobalID]
)
),
"NOT Ok",
"OK"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Nijlal01 , not knowing the cardinality of Table1, I use two unrelated tables and the following measure,
Result =
IF (
CALCULATE (
ISEMPTY ( Table1 ),
TREATAS ( Table2, Table1[Product], Table1[Customer] )
),
"NOT Ok",
"OK"
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
36 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
37 | |
29 | |
29 | |
24 |