DAX - VAR_Count_Filter Ex2

Mischa — Thu, 22 Jul 2021 14:21:47 GMT

Hello again,

An extention on my previous request,

Could I please request assistance with this problem:

Purchased date	Apple Type	Price	Rating	ANSWER
2021/07/08	Green Golden Apples	cheap
2021/07/09	Red Golden Apples	cheap
2021/07/10	Yellow Apples	expensive	Class 3
2021/07/10	Purple Apples	expensive	Class 3
2021/07/11	Blue Apples	expensive	Class 2
2021/07/10	Blue Apples	expensive	Class 1
2020/07/16	Green Golden Apples	cheap
2019/07/11	Yellow Apples	expensive	Class 1
2020/07/17	Red Golden Apples	cheap	Class 2
2020/07/18	Blue Golden Apples	expensive	Class 3
2019/05/08	Green Apples	cheap	Class 3
2019/05/09	Red Golden Apples	expensive	Class 2
2018/08/01	Yellow Apples	expensive	Class 1
2019/05/10	Purple Apples	cheap

What I want to calculate:

Only Indicate the class rating in the answer column

For the most recently purchased unique apple type,

IF the Rating is not blank,

Ex. If the most recent Yellow Apple was purchased on 2021/07/10, and it identified as a Class 3 Rating, the answer would then be Class 3 in that row.

The remainder of the Yellow Apple purchases will answer blank.

No doubt I will be further studying DAX after this 😂

Last Question for the day

Thank you so much

Re: DAX - VAR_Count_Filter Ex2

Jihwan_Kim — Thu, 22 Jul 2021 17:40:16 GMT

ANSWER CC =
VAR _currenttype = Data[Apple Type]
VAR _filtertable =
FILTER ( Data, Data[Apple Type] = _currenttype && Data[Rating] <> BLANK () )
VAR _mostrecent =
MAXX ( _filtertable, Data[Purchased date] )
VAR _rating =
MAXX (
FILTER (
Data,
Data[Apple Type] = _currenttype
&& Data[Rating] <> BLANK ()
&& Data[Purchased date] = _mostrecent
),
Data[Rating]
)
RETURN
IF (
Data[Purchased date] = _mostrecent
&& Data[Rating] = _rating,
Data[Rating]
)

https://www.dropbox.com/s/8culxo9z5iiyn4y/mischa.pbix?dl=0

topic DAX - VAR_Count_Filter Ex2 in DAX Commands and Tips

DAX - VAR_Count_Filter Ex2

Re: DAX - VAR_Count_Filter Ex2