Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Countifs Functiion (Dynamic by every row)

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
CountIfs
DAX
Dinamic rows
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous :

     

    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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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

        Regards,
        Harsh Nathani

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