DAX Friday is really cool !!! I enjoy learning new DAX expressions very much and you explain it so cool, even a noob like me understands 😉👍🏻 have a great weekend !!!!! 👍🏻
Hi Ruth, Can I put another calculate statement in place if nothing selected on filter,something like below. if(isfiltered(),calculate(), calculate ())???
+Hemant Singh Hi Hemant, The best way to answer that question is to try to do it yourself and see what happens. It is a great ( in my opinion the best) way to learn. Have a great weekend, /Ruth
Curbal ..I wrote this formula.. Curr_ytd = if(isfiltered(datetable(mnth-yr),calculate(total_sales,datesytd(datetable(date),"31/03"),calculate(tatal_sales,dates between(2017,04,01),(2017,09,30)) This formula is working correct only for true part of "if" and giving blank for the false part of "if". Also,if I break formula in to two parts i.e if I calculate the two calculate statements separately then both are giving correct result. I am just clueless as how to make it work. Can you guide me in right direction???
+Hemant Singh Hi Hemant, Post the question with some sample data in the power bi community. Make sure you post the Dax measure your are using to help troubleshoot, /Ruth
Your minor mistake was the good lesson for me (shifting new measure from wrong table to correct table) I wasn't aware of that option. Really thanks for your valuable video. :)
My function with the same formula doesn't return Total, I dont know why. Contribution point per teacher = IF( ISFILTERED('DIM - teacher'[teacher name]), CALCULATE(SUM('FACT - demo_day_score'[contribution_point])) ) Please help me fix this, thank you.
Hi Ruth, it would be interesting to see the difference between ISFILTERED /ISCROSSFILTERED / HASONEVALUE / HASONEFILTER in one of your next DAX Fridays. It is hard to figure out the difference here... Thanks.
+Victor Friesen Yes, that is a great idea 💡, I will add it to my to do list. In the meantime : powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/ /Ruth
Hello, Curbal! Can you make a video please for dynamic titles for hierarchies. That is, the title changes as we change the hierarchy level... thanks a lot!!
Buenas Ruth! Como se podría crear una formula para poder ver "Full previous year sales" aunque estemos filtrando en el mes de Abril en 2019 por ejemplo. Gracias de antemano!
Can you do a video to explain how to do cumulative or running total or sum of counts of text data. I have multiple years of data by years and months. The data is text, say “M”, “L” etc… in a column. I need to draw a chart showing both counts (bar) and running counts (line) by months. And from a slicer when I select a specific year then I should be able to visualize the counts and running counts of the text of the selected year. So running counts should be for a selected time say a year, not cumulative counts of all years starting from Min year to selected Max year.
Hello ! Thank you for the explanation, really nice tip ! I have a question, how to do the same when there is a hierarchy in the table rows : For instance, what if there is a period hierarchy in table's rows (Year > Months), and the slicer is filtering years ? Is it possible to display make the visual disappear until a year is selected ? Have a great week end
@@CurbalEN have already gone through this video but i don't want to show the grand total at all then what because if i have calculated a measure with multiple calculation using switch then grand total doesn't make any sense
for example follow powerpivotpro.com/2012/06/dax-making-the-case-for-switch/ link as we have calculation for previous yr ytd then grand total doesn't make any sense
Hi, The explanation was good liked it,,, can you please show calculation against blank/text...for eg.. if the year/month is not selected then the table should show current month data, and when the year/month selected then the table should show the selected year/month data... thanks.
@@CurbalEN Hi Ruth... Thanks a lot I was able to do the isfiltered... I created two measure with the condition I need and then I have added those two measure in the isfiltered function as you have demonstrated in the video ... Finally it worked.... Thanks a lot
DAX Friday is really cool !!! I enjoy learning new DAX expressions very much and you explain it so cool, even a noob like me understands 😉👍🏻 have a great weekend !!!!! 👍🏻
+SmartBimson Thanks SmartBimson! Your comments makes it work worth it :)
Have a great weekend !
/Ruth
What a great learning, Thanks a lot, Ruth.
Nicely explained with relevant examples. Keep the videos coming.
+Ali Hoolash Thanks Ali, have a great weekend!
/Ruth
Hi Ruth,
Can I put another calculate statement in place if nothing selected on filter,something like below.
if(isfiltered(),calculate(), calculate ())???
+Hemant Singh Hi Hemant,
The best way to answer that question is to try to do it yourself and see what happens.
It is a great ( in my opinion the best) way to learn.
Have a great weekend,
/Ruth
Curbal ..I wrote this formula..
Curr_ytd = if(isfiltered(datetable(mnth-yr),calculate(total_sales,datesytd(datetable(date),"31/03"),calculate(tatal_sales,dates between(2017,04,01),(2017,09,30))
This formula is working correct only for true part of "if" and giving blank for the false part of "if". Also,if I break formula in to two parts i.e if I calculate the two calculate statements separately then both are giving correct result. I am just clueless as how to make it work. Can you guide me in right direction???
+Hemant Singh Hi Hemant,
Post the question with some sample data in the power bi community. Make sure you post the Dax measure your are using to help troubleshoot,
/Ruth
Your minor mistake was the good lesson for me (shifting new measure from wrong table to correct table) I wasn't aware of that option. Really thanks for your valuable video. :)
Glad I didnt edit out the mistake then!
/Ruth
My function with the same formula doesn't return Total, I dont know why.
Contribution point per teacher =
IF(
ISFILTERED('DIM - teacher'[teacher name]),
CALCULATE(SUM('FACT - demo_day_score'[contribution_point]))
)
Please help me fix this, thank you.
Muchas gracias Ruth, esta muy clara su explicación del uso de la función!!!
+Norberto Vera Reatiga Gracias Norberto, Que tengas buen fin de semana!
/Ruth
Hi Ruth, it would be interesting to see the difference between ISFILTERED /ISCROSSFILTERED / HASONEVALUE / HASONEFILTER in one of your next DAX Fridays. It is hard to figure out the difference here... Thanks.
+Victor Friesen Yes, that is a great idea 💡, I will add it to my to do list.
In the meantime :
powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
/Ruth
Hello, Curbal! Can you make a video please for dynamic titles for hierarchies. That is, the title changes as we change the hierarchy level... thanks a lot!!
Is there an option to say IsFiltered([Field]="X","Yes","No")?
how did you opent he formula bar for PY sales?
Where do I do that?
/Ruth
Buenas Ruth! Como se podría crear una formula para poder ver "Full previous year sales" aunque estemos filtrando en el mes de Abril en 2019 por ejemplo. Gracias de antemano!
Hola Omar,
Me parece que lo explico en este video:
m.ua-cam.com/video/5WGOTsYngCI/v-deo.html
/Ruth
Can you do a video to explain how to do cumulative or running total or sum of counts of text data. I have multiple years of data by years and months. The data is text, say “M”, “L” etc… in a column. I need to draw a chart showing both counts (bar) and running counts (line) by months. And from a slicer when I select a specific year then I should be able to visualize the counts and running counts of the text of the selected year. So running counts should be for a selected time say a year, not cumulative counts of all years starting from Min year to selected Max year.
Hello ! Thank you for the explanation, really nice tip !
I have a question, how to do the same when there is a hierarchy in the table rows :
For instance, what if there is a period hierarchy in table's rows (Year > Months), and the slicer is filtering years ?
Is it possible to display make the visual disappear until a year is selected ?
Have a great week end
Tricky example for me, but got it working correctly :). Thanks!
You are on a roll 🎲🎲
@@CurbalEN it’s my day off 🎉🥳
What?? Put the computer down and go out in the 🌞. It is an order!
@@CurbalEN 😂🤣😅
how to handle the grand total when we are using isfiltered function, basically we should not be showing the grand total because it's not correct
Check this video:
ua-cam.com/video/ufHOOLdi_jk/v-deo.html&vl=en
/Ruth
@@CurbalEN have already gone through this video but i don't want to show the grand total at all then what because if i have calculated a measure with multiple calculation using switch then grand total doesn't make any sense
for example follow powerpivotpro.com/2012/06/dax-making-the-case-for-switch/ link as we have calculation for previous yr ytd then grand total doesn't make any sense
Got you!, in the Matrix visualization you can hide the grand total, I think it is hidden under subtotals tab.
/Ruth
@@CurbalEN that can be done in power BI but I am working on tabular cube so is there any option at design level to hide the grand total
very well done!
Glad you liked it :)
/Ruth
Hi, The explanation was good liked it,,, can you please show calculation against blank/text...for eg.. if the year/month is not selected then the table should show current month data, and when the year/month selected then the table should show the selected year/month data...
thanks.
Sure, I add it to my notes !
/Ruth
@@CurbalEN thanks
@@CurbalEN Hi Ruth...
Thanks a lot I was able to do the isfiltered... I created two measure with the condition I need and then I have added those two measure in the isfiltered function as you have demonstrated in the video ... Finally it worked....
Thanks a lot
Yey!!! Best way to learn, congrats!
/Ruth
@@CurbalEN thanks again
Great 👍
+renvangel Thanks to you revangel !
Month name column as gone for a toss
hi ruth please make video on Selectedmeasure()
Hi,
Selectedmeasure is for calculation groups and it us not possible to create them in power bi (natively) yet. I will wait for that to the the video.
Brilliant.
+Cloud Hound With the exception that i forgot to explain the function...😵
But Thanks!
/Ruth
Please show some energy while explaining things. slept before start