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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pluto556
Frequent Visitor

How filter a table that has the latest date and only include that in the count aggregation

Hi community, 

 

I have a bit of a complicated case here. I'm pulling in data from a model that runs everyday to score a metric. I'm pulling in the new scores to a dashboard that has a chart with the refreshed date as the x-axis and the number of products that exceed a score of 0.8. 

 

Here are some sample data I put together:

Screen Shot 2020-10-15 at 8.56.04 PM.png

Some products are not scored everyday. In fact, most products are not scored everyday. What I'm interested in doing is to count the distinct number of products that exceeded the score 0.8 on their latest score date. For example, product c received a score of 0.3 on 2020/09/13 so in the chart, c not counted as exceeding 0.8 eventhough previous scores may have been over 0.8. 

 

The chart of calculation looks like below:

Screen Shot 2020-10-15 at 8.56.10 PM.png

 

If you look at the date 09/07/2020, the number of discount counts of products with metric >= 0.8 is 3 even though 2 of the products scored on 09/07/2020 have metric exceeding 0.8. That's because product d was scored on 09/04/2020 but not on 09/07/2020 and d needs to be counted on the 09/07/2020 aggregation as well. 

 

In essence, the total distinct number of products scored remain at 4 unless new products other than a,b,c,d are scored. 

 

And I want to be able to visualize it like this:

Screen Shot 2020-10-15 at 8.56.19 PM.png

Any idea how I can achieve this on Power BI? 

1 REPLY 1
amitchandak
Super User
Super User

@pluto556 , Try like, hope these work

 

total product = distinctcount(Table[product])
product GT .8 = calculate( distinctcount(Table[product]), Table[Metrics] >.8)

percent= divide([product GT .8],[total product])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors