Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Please advice if you have can on how to convert Below table raw data which I have result table with DAX
I have raw data of the countries which sports they have, and I have summarised data with on the table using COUNTIFS function.
I would like to recreate the same table in Power BI using DAX function and get the same results.
The table summarised which sport country won and also on column N3 to N5 count how many Draws for last 10 days based on date column ("<"&TODAY()-50))
Please see below formulae which need to be converted to DAX
Summarised formulae
Please see attached Excel file which need to be converted to DAX formulae to get the summarised data on small table.
Highly appreciated for your help.
Excel file:with formulas on summarised table
https://app.box.com/s/z15jvl1tlfhm18jlacghdi6nbu3wk3h8
Power BI file/pbix
https://app.box.com/s/dfzieiqtw7r9dofa53pbqb30zqycl6yj
Solved! Go to Solution.
hi, @MYDATASTORY
First, when you load the data from excel, please choose "Table3" instead of the whole sheet.
Second, why "which sport country won" is USA? if it is the max [DRAW] of all country?
If so, please use these formula to create the measure
won country = var _maxdraw= CALCULATE(MAXX(VALUES(Table3[Country ]),[DRAW]),ALL(Table3)) return var _maxcountry=CALCULATE(MAXX(FILTER(VALUES(Table3[Country ]),[DRAW]=_maxdraw),[Country ]),ALLSELECTED(Table3)) return _maxcountry
DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(Table3,Table3[Status]="DRAW"))+0
More than 50days DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="DRAW"&&Table3[Date ]<TODAY()-50))+0
WIN = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="WIN"))+0
LOST = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="LOST"))+0
WIN% = DIVIDE([WIN],[WIN]+[LOST])
More than 50days win% = DIVIDE([WIN],[WIN]+[LOST]+[More than 50days DRAW])
Result:
and here is pbix file, please try it.
Best Regards,
Lin
hi, @MYDATASTORY
First, when you load the data from excel, please choose "Table3" instead of the whole sheet.
Second, why "which sport country won" is USA? if it is the max [DRAW] of all country?
If so, please use these formula to create the measure
won country = var _maxdraw= CALCULATE(MAXX(VALUES(Table3[Country ]),[DRAW]),ALL(Table3)) return var _maxcountry=CALCULATE(MAXX(FILTER(VALUES(Table3[Country ]),[DRAW]=_maxdraw),[Country ]),ALLSELECTED(Table3)) return _maxcountry
DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(Table3,Table3[Status]="DRAW"))+0
More than 50days DRAW = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="DRAW"&&Table3[Date ]<TODAY()-50))+0
WIN = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="WIN"))+0
LOST = CALCULATE(COUNTA(Table3[Country ]),FILTER(ALLSELECTED(Table3),Table3[Country ]=[won country]&&Table3[Status]="LOST"))+0
WIN% = DIVIDE([WIN],[WIN]+[LOST])
More than 50days win% = DIVIDE([WIN],[WIN]+[LOST]+[More than 50days DRAW])
Result:
and here is pbix file, please try it.
Best Regards,
Lin
User | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |