topic Re: how to get split column values based on the another table conditions in DAX Commands and Tips

how to get split column values based on the another table conditions

Anonymous — Tue, 24 Aug 2021 13:01:19 GMT

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 .

Re: how to get split column values based on the another table conditions

Greg_Deckler — Tue, 24 Aug 2021 13:43:59 GMT

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)

Re: how to get split column values based on the another table conditions

Anonymous — Tue, 24 Aug 2021 19:08:12 GMT

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.

Re: how to get split column values based on the another table conditions

Greg_Deckler — Tue, 24 Aug 2021 19:57:18 GMT

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.