DAX Fridays!

In todays video I will show you how to recreate the financial times graph in power bi where they compare the spread of confirmed cases after the countries hit their first 100 cases.
We will do the calculation using DAX and will plot the graph in Power BI.
Link to FT article: www.ft.com/con...
Link to how to get the data video: • How to import csv file...
The file is available for download in the download center - Community downloads number 56 or dax fridays 166. Please, use this dataset responsibly!!
Here you can download all the pbix files: curbal.com/don...
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/pro...
Many thanks in advance!Here you can download all the pbix files: curbal.com/don...
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE

[@darraghftown1]

really like the table you have during the video that displays the logic you are using Country, Date and Cases. Makes it easier to understand and to follow. :) thank you

[@CurbalEN]

Yes, I should do it on all videos, but sometimes time constraints prevent me from editing that way. /Ruth

[@Cedar2255]

Thank you. Ruth. I learned a lot about how to use filter and functions as well as data handling tips. very useful and handy. Hope you stay healthy and see you again soon!

[@CurbalEN]

Stay safe too! /Ruth

[@EricaDyson]

Yes, plotted different types of countries yet they all have similar trajectories. From cloudy Israel (in lock down)! Good job I have you, Ruth, to help me as I switch between knitting, Power Bi, and Adobe graphic design:-)

[@CurbalEN]

Yes, we all need to help out to decrease the pressure on hospitals and healthcare facilities. Stay safe Erica! /Ruth

[@KawanExcel]

Thanks Ruth... Curbal Always inspire me...

[@qianzhang8692]

Thanks Ruth!

[@CurbalEN]

My pleasure Quian! /Ruth

[@JK-ig2pp]

Your videos are very necessary, therefore, besides PowerBI, I am now learning English ))

[@CurbalEN]

Double gain! Stay safe and enjoy the weekend! /Ruth

[@markgoode9897]

Thanks Ruth. Timely and useful as always :)

[@CurbalEN]

Enjoy your weekend and stay safe! /Ruth

[@josepromero1452]

Hi Ruth, another interesting chart and KPI would be: increment of cases related with previous day. It also requires EARLIER function. For China, the trend line slope for this KPI is negative while for Spain and Italy is still positive.

[@CurbalEN]

It definitely is, I am going to do a weighted comparison of confirmed cases first and then your metric, thanks! /Ruth

[@josepromero1452]

@@CurbalEN I already saw the video... GREAT!!

[@CurbalEN]

@@josepromero1452 oh, I thought you meant something else, great! /Ruth

[@BryanCollins1]

Thanks for this one! I was trying to do something else with Covid data that I can't manage to accomplish. It's a super interesting chart which plots total confirmed cases on the Y and new cases on the X, both Log scales. The trouble is there is no line chart I'm aware of on Power BI that will allow you to place a measure on both axes, and I'm not having much luck making these as calculated columns. Appreciate any guidance if you can! Many thanks

[@totvabe1]

¡Estupendo Ruth!

[@CurbalEN]

👏👏👏

[@dirksmeyers877]

Thanks Ruth, very usefull!

[@CurbalEN]

Stay safe and inside! /Ruth

[@NaraMeerammaTrust]

Hi Ruth, I wanted to make it a dynamic to select the number instead of static number 100. I created a parameter using what if parameter.It is throwing error as we created a calculated column instead of calculated measure. I tried to create a measure, but it is throwing error as we have used earlier function in the code. Can you please direct me to have a parameter to work dynamically. Appreciate your help and response on it.

[@CurbalEN]

Mmmm, you can not change the 100 with the selected value measure? /Ruth

[@NaraMeerammaTrust]

@@CurbalEN Is there any other way to achieve it?

[@NaraMeerammaTrust]

It would be really helpful if we could make it dynamic. Let me know if we can do it by any other means. Is there a alternative way for Earlier function to achieve the same result so that i can create a measure instead of column

[@denismcsweeney187]

@@NaraMeerammaTrust Hi Venkatesh, try the measure below . It allows for a parameter to be used for selecting the number of cases reached and also for selecting a cut-off date. Days Since Cases Reached = VAR Country = SELECTEDVALUE(Location[Country/Region]) VAR CutOffDate = MAX('COVID-19 Cases'[Date] ) VAR CountryCases = SUMMARIZE( ALL('COVID-19 Cases'), Location[Country/Region], 'COVID-19 Cases'[Date], "Country Cases", SUM('COVID-19 Cases'[Confirmed]) ) VAR ReachedValueDate = CALCULATE( MIN( 'COVID-19 Cases'[Date] ), ALL('Calendar'), FILTER( CountryCases, [Country Cases] >= [CasesReached Value] && Location[Country/Region] = Country && 'COVID-19 Cases'[Date]

[@NaraMeerammaTrust]

@@denismcsweeney187 Thanks a lot Denis. Let me try with it. Thanks for your time.

[@powerbinareal]

Valeu Ruth.....Urra!!!!!!

[@CurbalEN]

🥳🥳🥳 /Ruth

[@powerbinareal]

@@CurbalEN vc é show....

[@adilmajeed8439]

how to pass a parameter in DAX while creating a calculated column (100th cases) in DAX, for example like 100 or 200 etc?

[@nagaray120]

Hola Ruth, muchas gracias por tus videos. Has notado algo extraño con la información? Los "recovered cases" en Estados Unidos no es cumulativo como si son "confirmed cases" or "deaths". Lo extraño es que cuando miro los "recovered cases" en otro país como China, los casos si son cumulativos... Te agradezco cualquier ayuda

[@CurbalEN]

No he revisado los datos de estados unidos, si encuentras anomalías puedes notificar a los autores en github y ellos te dirán. /Ruth

[@felipeturinofelipe6943]

Tks, I`m from Brazil, here there are a lot of people who are not being tested, big trouble.

[@CurbalEN]

Stay home and isolate yourself, that will make a difference if we all do it. Take care and stay safe!!!! /Ruth

[@javialpe]

Hi Ruth, i have a column With Cases ( numeric ) and another with Cases Names (like Confirmed, Death, Recovered). How i can do the filter >100?.

[@CurbalEN]

Just add to the 100 filter , overall cases [type] =" confirmed" That will filter out all other types. /Ruth

[@miltinhocamo3754]

Hola Ruth... estupendos vídeos, como siempre. Una ayuda. Creé el parámetro para dinamizar de 100 en 100 pero me da error. Indica que no acepta un valor booleano. Lo incluí dentro de un FILTER pero solo calcula el dato dejado por defecto del SELECTEDVALUE de la medida que se crea automáticamente. Que estaré haciendo mal?. De antemano mis agradecimiento.

[@CurbalEN]

I forgot that it was a calculated column. You can only use parameters in measures, sorry!

[@miltinhocamo3754]

@@CurbalEN Ok Ruth. Gracias. No lo sabía.

[@kealkil4]

What are the implications for storing the Date of 100th Case on the Location table? Will it create more complications than it will solve?

[@CurbalEN]

Calculated columns has its drawbacks, checking out here: m.kzbin.info/www/bejne/paLGiI2OrZxjp9U /Ruth

[@victorarayal]

Ruth, conoces alguna forma de agregar el país como etiqueta en el último punto gráfico de lineas? (como se ve en el ejemplo)

[@CurbalEN]

Intente hacerlo con charticulator pero solo funciona si todos días acaban en el mismo día:( No se de otra manera... /Ruth

[@jaimemolina1777]

Is this data accurate? If i create a table with Cases confirmed and Location the numbers seem off. For example, it says that China has 2,623,685 cases.

[@CurbalEN]

Yes, but the data is accumulated. Next week I will show you how to create a new cases column where you can sum. /Ruth

[@haarmartins]

Hi Ruth. I tried to publish the report for powerbi services, and although the visualization works on the desktop it is blank in services. The strangest thing is that if I download the pbix file from services and open it on the desktop it works. Can you help?

[@CurbalEN]

Is is a simple line chart, so it should work...can you delete it and try again? /Ruth

[@haarmartins]

@@CurbalEN I managed to get the visualization to work, going to Power BI Services, editing the report, editing the visualization and in the Axis variable I changed it to "Show items with no data". Then I changed the start of the Y axis to 100. This makes sense? I had already deleted and tried again.

[@CurbalEN]

@@haarmartins not really it should just show... Maybe a temporary bug? /Ruth

[@haarmartins]

@@CurbalEN Well, it's working and you can check it at: bit.ly/2xxK56s

[@DanielTavares29]

Hey Curbal !! i really like your videos ... and this was one of my favorites .. This topic (covid19) will be in your life for the next few days, unfortunately = / What I want to suggest for your channel is to do a deeper analysis with this data from the git hub .. show us how to recruit these measures from johns hopkins university coronavirus.jhu.edu/data (since we already have the data !!! hahahaha) That would be fantastic ... thanks for sharing your knowledge with us!

[@InkyPhys]

Datediff() negatives!

[@CurbalEN]

Make sure you put the start and end dates in the right position. /Ruth

[@InkyPhys]

Yes, previously would throw an error. Likely still does on power pivot.