Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a sales table that shows date, sales and marketID. I then have another table which shows marketID and countryname.
My tables are joined by marketID to retrieve the countryname. However, for any marketID that isn't matched in the market table, I would like to flag these as 'Other'. Is this possible?
Update: my market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.
Thanks!
Solved! Go to Solution.
Column =
VAR _conversionrate = RELATED(Market[conversionfigure])
RETURN IF(_conversionrate=BLANK(),1,_conversionrate)
Instead of Country put the conversion figure column name and in place of other put the default number that you want
Hi @bo_afk ,
I am not sure if I have missed something in your query, but perhaps in the query editor you could create a new Conditional Column called 'New Market ID' and, use the formula:
= Table.AddColumn(#"Expanded Sheet2", "Custom", each if [#"Sheet2.Countryname "] = null then "Other" else [Market ID])
This would then be able to replace your original Market ID column.
The way I tested this was to create two tables as you described, and merged the queries with a Left Outer Join.
"Sheet2" was the Market ID, Countryname table you described
Hope this helps, or if I not fully understanding your question, please let me know?
Kind regards,
Paul
Hi @paulvans182 @Anonymous , thanks for your resposes. There is an update to my query.
My market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.
Does this change anything to the suggestions you provided?
Thanks again!
Column =
VAR _conversionrate = RELATED(Market[conversionfigure])
RETURN IF(_conversionrate=BLANK(),1,_conversionrate)
Instead of Country put the conversion figure column name and in place of other put the default number that you want
@bo_afk Hope there is many to 1 relationship between Sales and market table please create following calculated column in sales table
Column =
VAR _country = RELATED(Market[Country])
RETURN IF(_country=BLANK(),"Other",_country)
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |