Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mischa
Frequent Visitor

DAX - VAR_Count_Filter Ex2

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture2.png

 

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 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Picture2.png

 

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 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors