Maximum value of a column

[Syndicate_Admin]

Hello

I'd like to know how to see the last value of one column filtering by others. I paste sample table.

Before:

ID0	ID1	Question	Answer
8	AAAVTUHJ	A	0
7	AAAVTUHJ	A	0
6	AAAVTUHJ	A	1
5	AAAVTUHJ	B	1
4	AAAVTUHJ	B	1
3	AAAVTUHJ	B	1
2	AAAVTUHJ	C	1
1	AAAVTUHJ	C	0

After:

ID0	ID1	Question	Answer	NewColumn
8	AAAVTUHJ	A	0	0
7	AAAVTUHJ	A	0
6	AAAVTUHJ	A	1
5	AAAVTUHJ	B	1	1
4	AAAVTUHJ	B	1
3	AAAVTUHJ	B	1
2	AAAVTUHJ	C	1	1
1	AAAVTUHJ	C	0

Greetings and thanks in advance,

Xavi

[v-lionel-msft · Accepted solution]

Hi @Syndicate_Admin ,

Please refer to the formulas.

//If you want a measure
Measure 4 = 
VAR x = 
MAXX(
    FILTER( ALL(Sheet7), [ID1] = MAX([ID1]) && [Question] = MAX([Question]) ),
    [ID0]
)
RETURN
CALCULATE(
    MAX(Sheet7[Answer]),
    FILTER(
        Sheet7,
        Sheet7[ID0] = x
    )
)

If you want a calculated column.

// If you want a column
Column = 
VAR x = 
MAXX( 
    FILTER( Sheet7, [ID1] = EARLIER([ID1]) && [Question] = EARLIER(Sheet7[Question]) ),
    [ID0]
)
RETURN
IF(
    [ID0] = x,
    [Answer], BLANK()
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

Hi @Syndicate_Admin ,

Create the followng calculated column as follows:

Output =

var t2 = CALCULATE(MAX('ID Data'[ID0]), FILTER(ALLSELECTED('ID Data'), 'ID Data'[ID1] = EARLIER('ID Data'[ID1])))

var t1 = CALCULATE(FIRSTNONBLANK('ID Data'[Question Answer], TRUE()), FILTER(ALLSELECTED('ID Data'), 'ID Data'[ID1] = EARLIER('ID Data'[ID1]) && 'ID Data'[ID0] = t2))

RETURN

IF('ID Data'[ID0] = t2, t1, BLANK())

Now move this to your table visual and you get the expected result:

Thanks,

Pragati

[Syndicate_Admin]

Hello

When I apply this formula the result is as follows:

For example, values 1 do not appear in the Output column

The formula is as follows (the table is called Label Oil)

Departure .

var t2 = CALCULATE(MAX('AceiteGráfica'[Id]), FILTER(ALLSELECTED('AceiteGráfica'), 'AceiteGráfica'[ExecutionReference] = EARLIER('AceiteGráfica'[ExecutionReference])))

var t1 = CALCULATE(FIRSTNONBLANK('AceiteGráfica'[Name], TRUE()), FILTER(ALLSELECTED('AceiteGráfica'), 'AceiteGráfica'[ExecutionReference] = EARLIER('AceiteGráfica'[ExecutionReference]) && 'AceiteGráfica'[Id] = t2))

RETURN

IF('AceiteGráfica'[Id] = t2, t1, BLANK())

[Syndicate_Admin]

Hi,

Question and Answer are a different columns.

[Pragati11]

Hi @Syndicate_Admin ,

Ok they are different columns I understand. But the output is right based on the requireemnt.

Thanks,

Pragati

[amitchandak]

@Syndicate_Admin , Create a new column

new column =
var _1 = countx(filter(Table, [ID1] =earlier[ID1] && [Answer] =0),[ID1])+0
var _2 = MinX(filter(Table, [ID1] =earlier[ID1] ),[ID0])
return
if([ID0] =_2 ,if(_1>0 0, 1),blank())

[Syndicate_Admin]

Hi,

When you writte "á" "?", what does a mean?

Regards,

Xavi