Question on Extract Group Values

[alvin199]

Hi all, 

I have a sample dataset that has 3 columns, ID, Food-A and Food-B. I would like to create a new column called Last Meal (Yellow color). I would like to exact the group of value by Sample column. For each Sample group, I want to determine the Last Meal column value from Food-A and Food-B.

The rule is if all values in Food-A is NA within the same sample, then display the Food-B set value in Last Meal column for the same sample, otherwise display Food-A column value within the same sample.
For example in sample A1, Food-A has 3 values are beef, lamb and NA (not all values are NA) so we will display Food-A as the Last meal within A1 sample. This is the same for sample A4. 
For sample A5, all the values in Food-A are NA so we will display Food-B values as the Last meal. 

Below is the data and expected value in Last meal (Yellow color):

I have tried using Conditional Column in Power Query Editor is not possible to filter within each sample. Using If-else statement is unable to filter by Sample too. 

Thanks for helping. 

[yogi678_1]

@ryan_mayu Another I want to clarify with you. 
The output of the Filter (to create the virtual table, I mean this FILTER(Sheet1, Sheet1[Sample]=EARLIER(Sheet1[Sample] && Sheet1[Food-A]=""), let's take A1 as example. Originally, it has 3 records, when applied the fillter function on whether Food-A column is NA, is the A1 record remain as 3 (beef, lamb and NA) or 2 (beef and lamb).

[ryan_mayu · Accepted solution]

@alvin199 

pls try this

Column = if(maxx(FILTER('Table','Table'[Sample]=EARLIER('Table'[Sample])&&'Table'[A]<>"NA"),'Table'[A])="",'Table'[B],'Table'[A])

pls see the attachment below

[yogi678_1]

@ryan_mayu I am trying to digest your solution.
Can you explain a bit why we need to use Maxx to know whether column A of the Table is empty ('table'[A]="") ?

Is the code below means using the Filter context, I am checking is each row is the same value as previous row (using EARLIER) and if the current row is the same as previous row in the Sample column then check whether column A is not NA?

Table'[Sample]=EARLIER('Table'[Sample])&&'Table'[A]<>"NA"

[ryan_mayu]

max to check if there is not NA value in the same sample,. If we can find any other value except NA, that will return reuslt, if we can't find, that will return blank. 

this is following your below logic:

The rule is if all values in Food-A is NA within the same sample

[yogi678_1]

@ryan_mayu Just to confirm my understanding is correct.

The Filter function will create a virtual table for each identical Sample. Then the Maxx function will give the maximum row count for each identical Sample of the virtual table and the maximum row count of the Sample actual table that the column A is not NA. If both the max count of the tables are the same (not having NA), then the Column A is empty. 

My questions is which code specify the compare of the max count of the virtual table and the table with column that is not NA? I am a bit of confuse, appreciate if you explain the breakdown the code. 

[ryan_mayu]

do not think too much about max, if you change to min, the DAX will be still working. you can separate the DAX to see the output of each part. 

check the output of 

maxx(FILTER('Table','Table'[Sample]=EARLIER('Table'[Sample])&&'Table'[A]<>"NA")

[yogi678_1]

@ryan_mayu I tried to change the virtual table to NA and it will use to evaluate the the Food-A column is empty (using the Test5 column). However, why A3 is giving NA despite A3 row will appear in the virtual table and it also become True when being used to evaluate Food-A column is empty, thus it shoud display Food-B column value, choco.

[ryan_mayu]

my dax is <>"NA"

Column = if(maxx(FILTER('Table','Table'[Sample]=EARLIER('Table'[Sample])&&'Table'[A]<>"NA"),'Table'[A])="",'Table'[B],'Table'[A])