Agree completely Pedro - in fact, I make it a rule to always hide any column in my fact tables that can be handled by a measure instead (Example - if I create a sum on quantity, then I hide the quantity column)
Answer to Q25 = #4 . 8000 pro licenses can cost up to US$ 80K/mo, but the Premium capacity would cost as "little" as US$ 5K/mo and free account users still can view the app (assuming all of these 8000 users are from the same organization...) And I finally won after 24 questions! 🎉 (...and 24 continues to be my lucky number!) Thank you so much for letting me start the weekend on an exciting note! 😄 Have a great weekend, Ruth!
Hi! Do you have a video explaining how to show sales from beginning of your calendar to selected date in the filter context. So like if I filtered to only show 2020 months in the column of my pivot table, i just want to see the total sales from beginning of time (Jan 1, 2017) to the given month. So for March 2021, the table would show sum of sales from Jan 1 2017 to Mar 1 2021 and so on and so forth. So lost on how to do this. Thanks!
Answer is 4 - configure app workspace for premium capacity. Sharing the app will require pro licenses for everyone and having 8000 Pro Licenses is more expensive than a premium capacity. App workspace in a premium capacity enables to share workspace with free users and since NOT everyone will be developing the report only a few Pro licenses are needed. $10 per license x 8000 users = $80,000 P1 Premium capacity is about $3000 + some developer licenses.
Hi Ruth /everyone I am trying to combine there excel sheet, first excel contains header, second and third excel sheet doesn't contains header, after combine this I am getting data from only first Excel sheet, Can you please help me how to combine these excel sheets? Or is it possible to combine data with header in one sheet and without header in another sheet ?
1:46 and Fiscal Calendar, and working days calendar. PBI shouls have those option has an auto calendars. I know Chris Webb long time ago published on his blog a M Query tu run a Calendar table or SQLBI guys published to do it in Dax. But, on my experience I ended up doing both ways just to get what I needed. Should be great to an option to create a Calendar Table where you can choose what type of granularity or level you want.
One annoying thing about using a calendar table is that if you have a 'sales' measure and a running total measure and the sales are not contiguous the RTM will fill in the same figure ; so if you have a sale on the 1st and the next or the 4th the 1st's figures will be repeated. The only way I've got round this is to use IF( [Sales Measure], [RT Measure] ) , does anyone know if there's a better way ?
Ruth. Thanks for your help with your videos specially the last ones with the questions ❤️. Yesterday, I pass the exam 🎉🎊 I am officially a PBI Microsoft Certified. Next step, take the Excel exam to achieve the MCSA certification. Any guidelines or example test for that one (70-779)?
I really wish they had called it "Date Intelligence" Since that is what it is. They just start introducing Time as a concept, in the most recent update.
1•Which is the best version of date table , either power query or Dax .. 2• How can I migrate the Dax date table with M query date table, with out affecting the measures & relationship?
1. Power query, this is why: m.th-cam.com/video/SmXLgEHXSGc/w-d-xo.html 2. You cant, if you move the calendar it will break everything. Worth the effort though... /Ruth
My fact table has different max dates for different countries, if i do not use bi- directional relationship it will always show incorrect max date for different countries, even when countries have different max date
Excellent timing! (Pun not intended haha!) I'm working on a project for my work which involves alot of Time Intelligence comparisons so this was super useful. A question for you though! I've always been curious about how filter context works when you're using a date table in a model which has multiple tables, which have multiple different datetime columns within. How does PowerBI know specifically how to filter the tables based off the selected range in the date table, when multiple date columns reside in different tables? I often end up with missing records. (The date table is always connected to my model using a date field rather than datetime)
If you have additional date columns that you want to use in your model, you will have to create inactive relationships for those. Hope I explained myself!
Ans is option 4 Configure app workspace to Premium Capacity.. Getting Pro licence for 8000 users is expensive.. Hence, cheaper way is upgrade the app workspace to Premium Capacity.
Anyone else wonder how/why the very same topic is recycled amongst several PBI Channels: Curbal, Guys-in-Their-[Microsoft Employee]Cubes, Avi Singh, RADACAD --- and likely more? Are they copying each other? Are they all in a professional relationship with Microsoft? Are they recycling MS' PBI Monthly Updates?
Weird you post this here, but let me see if I can help you along: I dont watch other channels to avoid what you mention. On the other concern, if we do any paid advertisement, it has to be disclosed to the audience, so if nothing is being disclosed then no money is changing hands. Finally, all you mentioned (except guy in a cube) including me, are microsoft MVPs. Hope this sheds some light to your questions/concerns. Enjoy your evening!
@@CurbalEN Thanks - the content is nearly exact across these channels, which, by-the-way, appear first in my PBI searches. That's my observation - seems more than random is my point: where else would I post it? At least you're not highlighting features without disclosing that they require a $5K/Mo Premium sub.... EEEYOOOOOO!!!!
I have more than 700 videos on the channel, it is inevitable that other people will make similar content. With that said, I think it is actually a good thing that you can hear from different people about the same topic to get different opinions and get different explanations! Enjoy your evening again :)
Me, a person that works with hourly data and needs a lot of moving average calculations for hourly windows proceeds to cry listening to point 3, realizing none of the time intelligence functions work for hourly data and that my report's performance is going to suffer.
Hey Ruth. I have a tip número nove: hide the date column in fact table....that way you never forget which date column to use
Yes, yes, yes, I will pin that :)
/Ruth
Agree completely Pedro - in fact, I make it a rule to always hide any column in my fact tables that can be handled by a measure instead (Example - if I create a sum on quantity, then I hide the quantity column)
Hi can please teach me how to run a dynamic dim table in a power query
your genuine happiness brought a smile on my face!! Thanks a lot!
😊
/Ruth
Answer to Q25 = #4 . 8000 pro licenses can cost up to US$ 80K/mo, but the Premium capacity would cost as "little" as US$ 5K/mo and free account users still can view the app (assuming all of these 8000 users are from the same organization...)
And I finally won after 24 questions! 🎉 (...and 24 continues to be my lucky number!) Thank you so much for letting me start the weekend on an exciting note! 😄
Have a great weekend, Ruth!
Same to you!!!
/Ruth
Great explanation, never knew about the idea behind using a date table for this function. Thanks for expanding on it more!
Option 4 is the right Answer Ruth. Awesome Video and this is very very useful and helpful for Power BI folks
Thanks!!
/Ruth
Marking my calendar table as a date table fixed my problem. Thanks!
Great tips ma'am, what if we actually need date/time column in our table to use sameperiodlastyear functionality?
Hi, i watch your videos. It is quite helpful. I hope you keep making Power bi dax videos. Thanks for sharing with us.
Allways an absolute pleasure to watch. How much have I not learend from you.
Thanks 😊!
/Ruth
Thanks Curbal for teaching us secret of power bi
Hi! Do you have a video explaining how to show sales from beginning of your calendar to selected date in the filter context. So like if I filtered to only show 2020 months in the column of my pivot table, i just want to see the total sales from beginning of time (Jan 1, 2017) to the given month. So for March 2021, the table would show sum of sales from Jan 1 2017 to Mar 1 2021 and so on and so forth. So lost on how to do this. Thanks!
hey, do you have tips to study and practice for a power Bi certificación? which exams there are? thanks so much.
MCSA BI REPORTING
Hi Ruth, even though I declare the table as date table . My time functions like same period last year etc not working.. please help??
I love the tips... Thanks Ruth
Agree with previous comments, answer is #4. Best, easy, quick and cheap way to achieve this task.
Noted :)
Answer is 4 - configure app workspace for premium capacity.
Sharing the app will require pro licenses for everyone and having 8000 Pro Licenses is more expensive than a premium capacity.
App workspace in a premium capacity enables to share workspace with free users and since NOT everyone will be developing the report only a few Pro licenses are needed.
$10 per license x 8000 users = $80,000
P1 Premium capacity is about $3000 + some developer licenses.
That was a detail explanation, thanks :)
/Ruth
Thanks for the awsome tips
Is there any way to create a calendar from using date column of fact tables(min and max dates) dynamically .
Please guide
This should work =CALENDAR (MINX(Sales,Sales[Order Date]),
MAXX(Sales, Sales[Order Date]))
i want this in edit Query where we can further play with Date option of transformation
@@shoeb1359 you can add columns in Dax using addcolumns functions
Hi, can you posted video that describe how to measure number of students who have absen more than equal 3 days in a month every year?
Hi Ruth /everyone
I am trying to combine there excel sheet, first excel contains header, second and third excel sheet doesn't contains header, after combine this I am getting data from only first Excel sheet,
Can you please help me how to combine these excel sheets?
Or is it possible to combine data with header in one sheet and without header in another sheet ?
@Ruth, Can you please guide me on above question?
#4 configure the worksapce for premuim capacity
Thank you Ruth
Thanks to you and noted :)
/Ruth
1:46 and Fiscal Calendar, and working days calendar. PBI shouls have those option has an auto calendars. I know Chris Webb long time ago published on his blog a M Query tu run a Calendar table or SQLBI guys published to do it in Dax. But, on my experience I ended up doing both ways just to get what I needed. Should be great to an option to create a Calendar Table where you can choose what type of granularity or level you want.
I think Miguel escobar has a custom connector that creates calendars. Check it out.
/Ruth
Ruth i love your video so much, gosto muito dos teu videos, please never stop, you're brilhant ,
from: Angola
Thanks Manuel! :)
/Ruth
One annoying thing about using a calendar table is that if you have a 'sales' measure and a running total measure and the sales are not contiguous the RTM will fill in the same figure ; so if you have a sale on the 1st and the next or the 4th the 1st's figures will be repeated. The only way I've got round this is to use
IF( [Sales Measure], [RT Measure] ) , does anyone know if there's a better way ?
Hi ruth, why calendar in powerquery and not made in Dax?
Here is why:
m.th-cam.com/video/SmXLgEHXSGc/w-d-xo.html
/Ruth
Curbal , so performance
Thanks for all the tips
Ruth. Thanks for your help with your videos specially the last ones with the questions ❤️. Yesterday, I pass the exam 🎉🎊 I am officially a PBI Microsoft Certified. Next step, take the Excel exam to achieve the MCSA certification. Any guidelines or example test for that one (70-779)?
Congrats!! 🥳🥳
There is probably a book for it?
/Ruth
@@CurbalEN Yes. There is a Microsoft boom for it. I bought weeks ago. Same style like the PBI green book.
I really wish they had called it "Date Intelligence" Since that is what it is. They just start introducing Time as a concept, in the most recent update.
Interesting thought, it actually has more to do with dates than time!
Happy Friday :)
/Ruth
1•Which is the best version of date table , either power query or Dax ..
2• How can I migrate the Dax date table with M query date table, with out affecting the measures & relationship?
1. Power query, this is why:
m.th-cam.com/video/SmXLgEHXSGc/w-d-xo.html
2. You cant, if you move the calendar it will break everything. Worth the effort though...
/Ruth
My fact table has different max dates for different countries, if i do not use bi- directional relationship it will always show incorrect max date for different countries, even when countries have different max date
Great tips! Thanks Ruth!
Thanks!
/Ruth
Its always refreshing to look at your face. Thanks for the Great Tips and the Tip Nove from @Pedro Cabral da Camara
Happy Friday Ruth,
Q24 -> 4 Configure the app workspace for Premium capacity. - Danny
Got it Danny :)
/Ruth
Thank you so much! :)
Tip 3.....if we don't use date column for relationship then how we will connect with calendar table with fact or dimension @Ruth
You can connect it with the field type text. It has to be formatted as date.
/Ruth
Yes create a blank pbi with date & supporting colums already in to be used each & everytime a date is needed
Yes, that is even better!
/Ruth
Excellent timing! (Pun not intended haha!)
I'm working on a project for my work which involves alot of Time Intelligence comparisons so this was super useful.
A question for you though!
I've always been curious about how filter context works when you're using a date table in a model which has multiple tables, which have multiple different datetime columns within.
How does PowerBI know specifically how to filter the tables based off the selected range in the date table, when multiple date columns reside in different tables? I often end up with missing records. (The date table is always connected to my model using a date field rather than datetime)
If you have additional date columns that you want to use in your model, you will have to create inactive relationships for those.
Hope I explained myself!
@@CurbalEN Yep! I know exactly what you mean.
Essentially creating an individual context per calculation using the desired date column.
Thanks 😊😊
Italian with Spanish accent is numero uno :)
😂😂
Well said .....
Respuesta: numero cuatro
Muchas gracias
Gracias a ti Jaime :)
/Ruth
Option 4!
Ans 4 : premium capacity app workspace :) . A mix of few pro licenses along with free viewership for other users.
Got it :)
Ans is option 4
Configure app workspace to Premium Capacity..
Getting Pro licence for 8000 users is expensive.. Hence, cheaper way is upgrade the app workspace to Premium Capacity.
Noted :)
/Ruth
your spanish sounds nice 😍
I am spanish, I was trying to do italian 😂😂
/Ruth
Answer is 4 - Configure the app workspace for Premium capacity
Noted!
nice!
Q25 - #4 - premium capacity would be cheaper than 8,000 Pro licenses and would handle the load better.
Noted :)
/Ruth
0:23 "Número 1" 😜 se te escapó.
Hi cartro, can you please guide me on above my question?
@@pandianselvaraj9204 Hi. Sorry, on which question? 🤔
Q25 >> option 4 : Configure App workspace for premium capacity. So that 8k users can access reports with as free user.
Noted :)
/Ruth
You are looking beautiful ! ❤️
Question 25: Answer: *4*
Got it!
Creo que la respuesta sería la número 4.
Gracias y buen finde :)
/Ruth
Question 25, the answer is 4
Noted :)
/Ruth
No 4 :)
Got it :)
/Ruth
Anyone else wonder how/why the very same topic is recycled amongst several PBI Channels: Curbal, Guys-in-Their-[Microsoft Employee]Cubes, Avi Singh, RADACAD --- and likely more? Are they copying each other? Are they all in a professional relationship with Microsoft? Are they recycling MS' PBI Monthly Updates?
Weird you post this here, but let me see if I can help you along:
I dont watch other channels to avoid what you mention.
On the other concern, if we do any paid advertisement, it has to be disclosed to the audience, so if nothing is being disclosed then no money is changing hands.
Finally, all you mentioned (except guy in a cube) including me, are microsoft MVPs.
Hope this sheds some light to your questions/concerns.
Enjoy your evening!
@@CurbalEN Thanks - the content is nearly exact across these channels, which, by-the-way, appear first in my PBI searches. That's my observation - seems more than random is my point: where else would I post it? At least you're not highlighting features without disclosing that they require a $5K/Mo Premium sub.... EEEYOOOOOO!!!!
I have more than 700 videos on the channel, it is inevitable that other people will make similar content.
With that said, I think it is actually a good thing that you can hear from different people about the same topic to get different opinions and get different explanations!
Enjoy your evening again :)
No. 4
Got it :)
/Ruth
Noted!
option 4 : Configure the app workspace in Premium capacity.
Noted!
Me, a person that works with hourly data and needs a lot of moving average calculations for hourly windows proceeds to cry listening to point 3, realizing none of the time intelligence functions work for hourly data and that my report's performance is going to suffer.
You can do calculations by the hour, they are just slightly more complicated and you can run into performance issues quite quickly :(
Answer is 4
Noted :)
/Ruth
My answer is 4
Noted :)
4
Noted
Cute