CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
user151387
user151387

Reputation: 103

How can I calculate a group by aggregate, while filtering the original data?

enter image description here

I am working on a Coronavirus dataset with this structure, where each country has records starting from the date of the first known recorded cases. The case column is not aggregate, so it carries the recorded cases of that day. This suits itself to a chronological comparison between countries, but I would also like to compare them starting from the dates where each of the countries recorded their first cases. This is why I would like to compute a new column via DAX that adds a column with the date at which the corresponding country recorded its first cases.

My first attempt:

Date of First Case = CALCULATE(MIN(Corona[dateRep]); ALLEXCEPT(Corona; 
    Corona[countriesAndTerritories]))

returns the earliest date of each countries' records.

However, I don't know how I could apply a filter here such that the records with 0 cases are not taken into account for the earliest date.

How can I calculate the first date for every country where more than 0 cases were recorded and add it as an additional column to the table?

Upvotes: 1

Views: 344

Answers (1)

Saaru Lindestøkke
Saaru Lindestøkke

Reputation: 2544

You are almost there with your attempt. The additional condition can be added as an argument to the CALCULATE function:

Date of First Case = 
CALCULATE(MIN(Corona[dateRep]); 
    ALLEXCEPT(Corona; Corona[countriesAndTerritories])
    Corona[cases] > 0)

Credit goes to this PowerBI Community message

Upvotes: 1

Related Questions