Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |