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.
Hello All
I need your help in creating the calculated column as below.
If Table 1 Product Code = Table 2 Product code
and
Table 1 product type 16 = “1” in (table 3 city type)
then
Match
Table 2 city code with Table 3 city code and return table 3 country
Else return the country from “0” in (table 3 city type)
For any errors or mismatches return the value as “Mismatch”
Table 1 | Table 2 | Table 3 | ||||||
Product Code | Product Type | Product Code | City Code | City Type | City | Country | ||
0 | 10 | 0 | AAC | 0 | AAC | AFRICA | ||
1 | 11 | 1 | AAE | 0 | AAE | AFRICA | ||
2 | 12 | 2 | AAE | 1 | AAE | MALDIVES | ||
3 | 16 | 3 | GLE | 1 | AAI | MALDIVES |
Hi @gauravnarchal ,
Is your issue solved?
I'm not sure about the logic of 'Table 1 product type 16 = “1” in (table 3 city type)'.
Could you provide more details?
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
not clear about this. Could you please separate the table in different rows? It's hard to understand how many columns in each table
Proud to be a Super User!
@ryan_mayu - Here is how the data look like in the separate tables.
Thank you
Table 1
Product Code | Product Type |
0 | 10 |
1 | 11 |
2 | 12 |
3 | 16 |
Table 2
Product Code | City Code |
0 | AAC |
1 | AAE |
2 | AAE |
3 | GLE |
Table 3
City Type | City | Country |
0 | AAC | AFRICA |
0 | AAE | AFRICA |
1 | AAE | MALDIVES |
1 | AAI | MALDIVES |
which column is the expected result?
Proud to be a Super User!
Hi @ryan_mayu Please see below.
If Table 1 Product Code = Table 2 Product code
and
Table 1 product type 16 = “1” in (table 3 city type)
then
Match
Table 2 city code with Table 3 city code and return table 3 country
Else return the country from “0” in (table 3 city type)
For any errors or mismatches return the value as “Mismatch”
DATA
Table 1 | |
Product Code | Product Type |
0 | 10 |
1 | 11 |
2 | 12 |
3 | 16 |
Table 2 | |
Product Code | City Code |
0 | AAC |
1 | AAE |
2 | AAE |
3 | GLE |
5 | AMR |
0 | 28JU |
0 | JKLW |
0 | "2JK |
Table 3 | ||
City Type | City | Country |
0 | AAC | AFRICA |
0 | AAE | AFRICA |
1 | AAE | MALDIVES |
1 | AAI | MALDIVES |
0 | GLE | INDONESIA |
1 | GLE | BHUTAN |
RESULT
Table 2 | ||
Product Code | City Code | Country |
0 | AAC | AFRICA |
1 | AAE | AFRICA |
2 | AAE | AFRICA |
3 | GLE | BHUTAN |
5 | AMR | MISMATCH |
0 | 28JU | MISMATCH |
0 | JKLW | MISMATCH |
0 | "2JK | MISMATCH |
still not clear about the calculation logic.
do you mean check the product code in table 1 based on the product code in table 2?
the line 4, we can see 3 in table 1 , then we found the corresponding product type which is 16. 16 =1, then go to table 3 check city type is 1.
Then what about 10 11 12?
Proud to be a Super User!
Hi,
In which Table do you want to see that calculated column? In that table, show the expected result.
I know this isn't directly answering your question, but I recommend setting up the relationship in the data model itself. Link the Product Codes together in the data model, and link the City Codes together in the model.
If you do that, then you don't need a formula, you can simply grab the Country code and it will work automatically.
If you need to filter by Product Type 16, you can add that as a filter to the visual.
@viviank - I need to create a calculated column for getting the desired result. The visual filter will not work.
Also, in table 3 there are cities which had got different countries.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |