CODEWITHSUNDEEP
powerbidax
SQLfun
SQLfun

Reputation: 53

How to convert COUNTIFS Excel formulae to DAX to get summarized data

I have raw data of the countries which sports they have, and I have summarized data using COUNTIFS function. I would like to recreate the same table on Power BI using DAX formulas and get the same results.

The summarized table which has sports country has (Won,Draw and Lost) and also on column N3 to N5 count how many Draws for the last 50 days based on date column. Below are formulas which need to be converted to DAX: 

=COUNTIFS($B$4:$B$34,$L$3,$D$4:$D$34,$N$2) 
=COUNTIFS($B$4:$B$34,$L$3,$D$4:$D$34,$N$2,$E$4:$E$34,("<"&TODAY()-50))

I have attached an Excel with summarized table which I am trying to recreate using Power BI, all the Excel formulas have been left on summarized table in order to show how I did work out the final figures/numbers:

Excel file with formulas: https://app.box.com/s/z15jvl1tlfhm18jlacghdi6nbu3wk3h8

Pbix File: https://app.box.com/s/dfzieiqtw7r9dofa53pbqb30zqycl6yj

Upvotes: 0

Views: 334

Answers (1)

Rick Grimes
Rick Grimes

Reputation: 113

To have a similiar functionality like COUNTIFS you can use something like that:

RESULT = CALCULATE (
    COUNTROWS ( Tablename ),
    FILTER (
        Tablename,
        Tablename[country] = country_name
            && Table[sport] = sport_name
    )
)

Or create a Matrix and use your countries as rows, attributes (win, draw, lost) as columns. Matrix can be found under visualizations.

Upvotes: 0

Related Questions