Forum Discussion
how to get split column values based on the another table conditions
I having two table both sheet gave a common value Market code
1.mergedsheet
2.Monthly rep
Merged sheet have values filed actauls
mergedsheet
KEY MARKETCODE ACTAULS
1A04 FS 450
2A04 KS 450
3A04 LS 450
4A04 0S 450
Monthly rep
KEY MARKETCODE DS/NONDS
1A04 FS DS
2A04 KS NONDS
3A04 LS DS
4A04 0S NONDS
now i want split mergesheet table based on the monthly rep[Ds/NONDS] . monthly rep market value matched rows with ds condtions in monthly rep its split new column
mergedsheet
KEY MARKETCODE DS NONDS
1A04 FS 450 0
2A04 KS 0 450
3A04 LS 450 0
4A04 0S 0 450
i tired lookup value with filter context not working. any idea .
Anonymous You could use a Merge query in Power Query to do this probably. For DAX columns, try:
DS Column = VAR __Key = [KEY] VAR __Market = [MARKETCODE] VAR __DSNDS = MAXX(FILTER('Monthly rep',[KEY]=__Key && [MARKETCODE]=__Market),[DS/NONDS]) RETURN IF(__DSNDS = "DS",[ACTAULS],0)
3 Replies
- Greg_Deckler
Community Champion
Anonymous You could use a Merge query in Power Query to do this probably. For DAX columns, try:
DS Column = VAR __Key = [KEY] VAR __Market = [MARKETCODE] VAR __DSNDS = MAXX(FILTER('Monthly rep',[KEY]=__Key && [MARKETCODE]=__Market),[DS/NONDS]) RETURN IF(__DSNDS = "DS",[ACTAULS],0)Greg_Deckler tired this measure but i am getting error DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
- Greg_Deckler
Anonymous As an experiment,replace your RETURN with just 1 and see if the error goes away. Seems like everything being compared should be text.