Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
How to get the number of a given column for a specific date?
I'm trying to calculate the number of Covid-19 confirmed cases per country on the first day the disease has been detected.
This first day varies by country and it is correctly calculated as:
Data do 1º caso = CALCULATE(MIN('Covid19'[Data]);FILTER('Covid19';[Total Casos Confirmados]>0))
The expression Total Casos Confirmados above is:
Total Casos Confirmados = CALCULATE(SUM('Covid19'[CasosConfirmados]);LASTDATE('Covid19'[Data]))
And the measure I'm trying to calculate is:
Número de casos no primeiro dia = CALCULATE(SUM('Covid19'[CasosConfirmados]);FILTER('Covid19';'Covid19'[Data]= [Data do 1º caso]))
And this is how Covid19 table looks like:
And the Wrong result:
Since the table is a time series with cumulated values, the measure is returning the acumulated value for all the dates..
Solved! Go to Solution.
Try like
Measure =
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ), 'Covid19'[Country/Region] = __id ,'Covid19'[CasosConfirmados]>0)
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )
@webportal , for case on first date country wise
Measure =
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ), 'Covid19'[Country/Region] = __id )
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )
Try like
Measure =
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ), 'Covid19'[Country/Region] = __id ,'Covid19'[CasosConfirmados]>0)
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |