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
Anonymous
Not applicable

Countifs Functiion (Dynamic by every row)

Countifs.JPG

1. I want to count "every date" by "each country" when "Confirmed column is nonblank or >0". In excel file we can do it by countifs function. What would be the Dax formula for this solution?

 

DateConfirmedRecoveredDeathsDay of YearProvinceCountryCorona Days (Expected Result)
22-01-205    Bangladesh1
22-01-20     Sweden0
22-01-20     US0
22-01-20     UK0
22-01-20417    India1
22-01-20694    Italy1
22-01-20411    Denmark1
23-01-20229    Norway1
23-01-20146    UAE1
23-01-20265    Bangladesh2
23-01-20388    Mali1
23-01-20358    Italy2
24-01-20599    UK2
24-01-20371    Bangladesh3
24-01-20776    Sweden1
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @harshnathani :

 

Kudos and Thanks a lot for your support. Finally I have got my expected result by the following formula:

 

Corona Days =
if('Covid-19 Cases'[Confirmed]<=0,0,(
calculate(
DISTINCTCOUNT('Covid-19 Cases'[Date]),
(FILTER('Covid-19 Cases','Covid-19 Cases'[Country/Region] =
EARLIER('Covid-19 Cases'[Country/Region]) && 'Covid-19 Cases'[Date] <=
EARLIER('Covid-19 Cases'[Date]) && 'Covid-19 Cases'[Confirmed]>0)))))
 
Without using "Distinctcount" function, proper number was not coming as some of the countries had entry on the state column. On that case, counting date was giving unexpected numbers. you can suggest if there is more efficient dax for it.
Regards
Capture 2.JPG

View solution in original post

9 REPLIES 9
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

You can try

 

DC =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Date] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[Country]
        ),
        'Table'[Confirmed] > 0
            || NOT (
                ISBLANK ( 'Table'[Country] )
            )
    )
)

 

 

Pls share sample data in text format incase this does not work

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Hi Harshnathani: This DAX returning the total days for a country. But I am expecting to get the number of days as I have updated in another table. (after pasting the table the date column is getting distorted 😞 )

Hi @Anonymous ,

 

Please create a measure

 

DC =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Date] ),

FILTER(
ALLEXCEPT (
'Table',
'Table'[Country]
),

MAX('Table'[Confirmed]) > 0
|| NOT(ISBLANK(MAX('Table'[Confirmed])))
 
))
 
 
1.jpg
 
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Hi @harshnathani : Appreciate your faster supports. But seems I am not yet getting expected value. Its same value coming in every cell 😞

Capture 2.JPG

@Anonymous ,

 

 

Can you share expected output.

 

I had shared the formula for a measure.

 

Regards,

Harsh Nathani

Anonymous
Not applicable

@harshnathani 

 

I am basically expecting the output like the last column.

 

Capture.JPG

Hi @Anonymous ,

 

 

Missed replying back yesteday, had an electricity outage.

 

Here are your results.

 

Create a new Calculated Column.

 

Output =
CALCULATE(COUNT('Table'[Confirmed]),(FILTER('Table', 'Table'[Country] = EARLIER('Table'[Country]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Confirmed] > 0)))
 
 
1.jpg
 
 
UK should be 1 on 24th,2020.
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Hi @Anonymous ,

 

And incase you want 0's in front of Sweden,US,UK.

 

Output =


var a = CALCULATE(COUNT('Table'[Confirmed]),(FILTER('Table', 'Table'[Country] = EARLIER('Table'[Country]) && 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Confirmed] > 0)))

var b = IF (ISBLANK(a),0,a)
Return
b
 
 
Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

Hi @harshnathani :

 

Kudos and Thanks a lot for your support. Finally I have got my expected result by the following formula:

 

Corona Days =
if('Covid-19 Cases'[Confirmed]<=0,0,(
calculate(
DISTINCTCOUNT('Covid-19 Cases'[Date]),
(FILTER('Covid-19 Cases','Covid-19 Cases'[Country/Region] =
EARLIER('Covid-19 Cases'[Country/Region]) && 'Covid-19 Cases'[Date] <=
EARLIER('Covid-19 Cases'[Date]) && 'Covid-19 Cases'[Confirmed]>0)))))
 
Without using "Distinctcount" function, proper number was not coming as some of the countries had entry on the state column. On that case, counting date was giving unexpected numbers. you can suggest if there is more efficient dax for it.
Regards
Capture 2.JPG

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