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

pluto556 — Fri, 16 Oct 2020 04:08:07 GMT

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:

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:

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:

Any idea how I can achieve this on Power BI?

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

amitchandak — Fri, 16 Oct 2020 04:44:12 GMT

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])

topic Re: How filter a table that has the latest date and only include that in the count aggregation in DAX Commands and Tips

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

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