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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Finding the values for most recent transaction date, then finding their percent of all recent values

Hello, I am rather new and looking to figure out this via measure(s). I can't seem to find a suitable answer in past answers that's working for me. Also, please let me know if this is something better tackled via query.

 

I am working with the first table, and looking to create the third matrix. The middle filtered table is just for context. I need to first isolate the most recent dates and grades, then I need to understand how to calculate what only the most recent gradings are as a percentage of A, B, and C.

 

Start:

GradeRestaurantDate
aBob2/15/2022
aBob1/15/2022
cBob3/1/2022
bSue12/13/2021
aSue2/1/2022
bSue3/3/2022
cGreg2/1/2022
aGreg1/31/2022
cGreg1/15/2022
bMiss1/8/2022
bMiss1/9/2022
aMiss1/20/2022
cJon2/3/2022
aJon1/3/2022
bJon3/3/2022
bPam12/2/2021
aPam9/8/2021
cPam6/2/2021

 

Isolate latest:

GradeRestaurantDate
cBob3/1/2022
bSue3/3/2022
cGreg1/31/2022
aMiss1/20/2022
bJon3/3/2022
bPam12/2/2021

 

Final Matrix:

Grade% Restaurants (Latest)
a17%
b50%
c33%

 

Thank you in advance to all that help!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Please find the file attached with solution after signature

 

View solution in original post

3 REPLIES 3
v-easonf-msft
Community Support
Community Support

Hi @Anonymous 

ALLSELECTED : Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Regarding MAX, my understanding is that returning the [Restaurant] of the current table is compared to the [Restaurant] in the virtual table, and when the value of 2 [Restaurant] are the same, the largest date is returned .

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , Please find the file attached with solution after signature

 

Anonymous
Not applicable

Hi, Thank you so much for the response!

 

I just have one point of confusion so far, about the measure(s) which are getting the Last Date/Grade. Below is how I'm reading it an my question:

 

Last Date is doing the following:

 

Creating a variable (_max) for the most recent date

  • MAXX finds the latest date (expression Date[Date]) for rows in the table (FILTER function)
    • FILTER reduces data and produces a table for MAXX by the expression (MAX of the restaurant)

 

  1.  What I guess I don't understand is, what is the MAX function performing? Is it just returning 1 of each of the restaurants?
  2. Also, what is the purpose of ALLSELECTED?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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