I am looking for a dax to remove row context in measure by keeping the filter context.

[SatishY]

I have 2 tables one fact and dim, and below is how it is appearing in the powerBI table visual.

I need to have only the lowest marks record per student within the selected date filter.

Sampl1: if i select Jan and Feb, it should display below 2 records:

Satish   75   1/1/2023

Selva    47    1/1/2023

Sample2: if i select jan , feb, and march it should show below records:

Satish   50   3/1/2023

Selva    47    1/1/2023

Sample3: if i select  feb, and march , april , below is expected.

Satish   50   3/1/2023

Selva    54   3/1/2023

Sample4: without any date filter, below is expected.

Satish   50   3/1/2023

Selva    47    1/1/2023

Currenly i am using below formula, but something is wrong in it and not getting the desired result.

Min of Marks min per SID = MINX(KEEPFILTERS(VALUES('dim'[SID])), CALCULATE(MIN('fct'[Marks]),ALLSELECTED(fct)))

Any help would be highly appreciated. Thanks in advance!

[Anonymous]

Hi @SatishY ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Min of Marks min per SID = CALCULATE(MIN('fct'[Marks]),FILTER(ALLSELECTED('fct'),'fct'[Date] in VALUES('fct'[Date]) && 'fct'[Student]=MAX('fct'[Student])))

min date = 
 LOOKUPVALUE(fct[Date],fct[Marks],[Min of Marks min per SID])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[SatishY · Accepted solution]

The expected functionality is disrupted when I remove the Satish record dated 1/1/2023. Even after applying the mentioned measure, the display still indicates March marks are displaying under 1/1/2023, which is wrong.

On the contrary, the solution that I successfully implemented is as follows:

MIN_Marks =

var mind = CALCULATE( min(fct[Marks]),ALLSELECTED(fct[Date]))

RETURN

CALCULATE(SELECTEDVALUE(fct[Marks]), fct[Marks] = mind)

[SatishY]

Thanks for responding @amitchandak 

The proposed solution is working partially, it is looking for min marks and date for the whole dataset, but I need it for each student.

For ex: if there is no January record for satish in the above dataset. It is still giving me 1st jan 2023 record for satish with empty marks:

[amitchandak]

@SatishY , Use TOPN

TM  = sum(Table[Marks])

TOP 1 Date = Minx(TOPN(1,allselected([Table[Date]), [TM1], asc) , [Date])

Top1 marks = Calculate( [TM], keepfilter(TOPN(1,allselected([Table[Date]), [TM1], asc) ))

Use both measures with student

Or use second measure in visual as well as visual level filter