Add measures in bulk to your Power BI file without external tools
Вставка
- Опубліковано 22 лип 2024
- In todays video I am going to show you how to add measures in bulk to Power BI without using external tools. Why you may ask?
Well, it is very common that users can not install third party software on their computers or you might not want to learn a new tool.
Enjoy!
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
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
☼☼☼☼☼☼☼☼☼☼
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/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
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/product/sponsor-me
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
Thanks so much for this - saved me a lot of time. I was able to move the measures in DataModelSchema file into a single line in Notepad++ -- then I copied it into excel and was able to copy it into 100+ rows and do some magic to fill in my criteria for each. Then copied back into the file and woola! Awesome trick. One note is I had to change the "lineageTag" for each measure or else I couldn't open the pbit file. This was easily accomplished by just changing the last 3 digits of the Tag on each measure and counted up in excel.
This is an incredible hack, I'm so happy that it actually worked (Jan 2024)! I had over 100 measures that I was able to copy'n'paste from the schema file. Thanks for the tip!
OMG!!!! I LOVE YOU!!!!!! You have made our lives soooo much easier!
Did I say I love you?
Hello, I am new to the power bi desktop. I have been working on making dashboard on power bi. I struggle a lot I didn't know how we can copy paste the measure file. Now I know how we can copy/paste the measuring files from watching your videos.
Thank you for an very interesting and useful video, but I have a question: does it possible to insert a measures value in to the cells of the loaded table instead an existing values in these table?
Thank you for this video. Using it, i have built a method to switch a dataset from Import mode to directQuery mode.
I will send it to you, it is very simple.
you are a gem
Very cool tip. I got lost when you wre ecutting and pasting the measure in notepad. Can you explain again what you were doing theer? Did you replace the measures with ones referncing the new table? And what was it we're to do with annotations? thanks!
Thank you so much for the tip. It helped to create more than 300 measures without using external tools :)
🥳🥳
What if I just want to add in the measure to a different report? Can I pick and choose?
Thanks for such a Marvelous Trick
Nice trick Ruth, not few people but there are a lot of people in big organization where external tools are not allowed or need many approvals.
Then this trick is perfect!
If we change the measures from one table name to new table name then the table names in the measures changes ?
This was awesome. I would like to make a suggest to get the JStool for Notepad++ which might make it easier to traverse the model using the JSON viewer.
Thanks for the tip :)
Thanks Ruth for sharing your ideas
Always my pleasure :)
You saved me. Thanks a lot 😊
At your service :)
Can we change columns dax?
This is what I was looking for. However, when i open data model file in notepad++, it is unreadable format. Does anyone know how to fix it?
This again is a lovely hack Curbal. I have two questions though (apologies for being a little thick here as I am starting out on DAX) :
1. Will the same method work in case of calculated columns and the reason I am asking this question is below in the 2nd one :
2. I just cleaned up a dataset and then the next one comes in with a completely different structure and column headers :
a. will I need to clean up every new data set manually as they keep coming?
b. Is it possible to force the new columns to conform to the existing column structure and names as in the cleaned up query which can serve as a template for all new ones
c. Can the calculated columns on the existing query be copied and re-used on all the new in-coming queries?
Thanks and sorry for having taken too much space here
Just I wanna mention that this solution is NOT working for Direct query connection. I've tried Imported conn and it's working. Thank you
Could you please make a video on how to copy measures in bulk from one pbix file to another pbix file?
Realy great! Thank you
🥳🥳
Interesante, ¡Gracias Ruth!
Un placer!
Excelent Ruth!
"Come on baby!" - I like it when Ruth encourages her file to perform.
That is how we improve our connections isn't it? 😂😂
Pretty cool for bulk measure creation. Nice work
😂😂 that needs to be added to performance best practice!!
Thanks for this - we've successfully used this approach to create new bulk measures in the past but can now see that measures expect a 'lineageTag' in the schema. We've tried using null against it but with no luck - we get a 'cannot deserialize database' error when we open the edited pbit file. Wondered if you had resolved any such issue.
same problem here. did you ever work it out?
PERFECT* thanks Ruth
❄❄
Hola Ruth, un video muy interesante!
He visto que te preguntaban
sobre los problemas que podrían surgir con los visuales. En el caso de las medidas, al cambiar la referencia de la tabla no hay problema (puedes borrar la tabla Order_details una vez cambiadas todas las referencias), pero la duda me surge con las columnas.
Si yo tengo un visual con la columna 1 de la tabla Order_details, y borro dicha tabla este visual se rompe, da igual que tenga esa misma columna en Order_details2.
Si es solo para un visual no pasa nada, pero como tengas muchos, hay que rehacerlos todos :(
¿Alguna sugerencia para evitar esto? ¿Se puede acceder a los metadatos de los visuales para cambiar
sus referencias de manera más eficiente?
Gracias!
This is crazily nice!!! :D :D
🥳🥳
I get an error, the JSON input is not properly formed
same. @curbal any idea why this is?
Thanks, this looked very promising, but it did not work when trying to copy measures between files, I suspect that there is underlying code which needs to also be changed. In any case, thanks for a way to get at the underlying code, I suspect this will help in the long run.
hi, I want to ask that have you found the solution to this problem? I now have encountered this, and it’s really hard to deal with without that external tool (it’s hard to get permission to install it on my working computer because of company policy issues) I am looking forward to your reply!
We are planning to have two separate premium capacity nodes in our project, one for Dev and Testing and another is for production,
Can you please let me know what impact this may have on deployment pipeline ? Can we configure a pipeline to talk to two different nodes?
I dont know, Try asking in the power bi community!
Muito bom parabéns.
Obrigada :)
Thank you for the very helpful video! Question, In case some measures are used in visuals, is there also a method to replace all the old measures in a visual with the new ones in bulk?
Name them the same and it will work :)
@@CurbalEN I see, the names are the same as they were, but the visual does not show data, instead there's the error "This field can't be used since it is invalid". And so I have to manually replace the field in the visual to the new one. What am I missing? :) Thank you very much!
Maybe the data type is wrong?
Hi Ruth, Ca you please make a video on "CI/CD pipeline in GitLab that talks to Power BI Service and updates the ‘Development’ workspace of the project" (GitLab CI/CD integration with Power Bi deployment pipeline)
Mmmm wouldn't know where to start, have you asked guy in a cube guys?
@@CurbalEN No.
Try to see if they can assist on that!
Hi, greetings, how about bulk copy of calculated columns? Is it also possible this way?
Nope :(
@@CurbalEN Do you have any other idea how we may bulk copy calculated columns between two pbix files? The external tool that is supposed to do automate this task - ALM Toolkit - does not see calculated columns.
Maybe tabular editor?
Hi Ruth, what software do you use to record and edit your videos?
Camtasia
@@CurbalEN Eres un amor! =)
Hi Ruth, can this be done copying the dataschema to another pbix file?
Probably?
@@CurbalEN Yes I achieved it. It also worked for copying measures from one PBIX file to another. Only thing you need to do is make sure measures have correct table names. You can do that using Notepad++ with Find and Replace. Also make sure to rename Untitled PBIX file to something useful name.
Well done 👏
3:10 Nice trick!!!!
Which version of PBI is this? When try to extract zip I dont see DataModelSchema. I can see only DataModel file which when opened in Notepad++ shows some weird characters
Don't fast forward the video, you will miss important steps!
Ruth, you should run one more minute with this video to show what were the broken visuals problems (reference to a measure should remain, right? Or it is by some GUID created "on-the-fly")
but anyway, many thanks for this trick :)
It is probably visuals with measures mentioning the table name. You need to change it from Order_Details2 back to Order_Details.
That should do it. Test it and let us know.
YAAAAAAAAAAAAAYYY !! ME FIRST :) ONE QUICK QUESTION : What is the best way to import data of five years when we have 123 columns and more then 30Mil rows . I am writing SQL query by Import method in Power BI and bringing the data but for one fiscal year the PBI file size has become 225 MB which is really heavy and customers wants to see the last five years financial information with comparison !! Thanks again for sharing this Ruth !
Yey!!🥳🥳
@@CurbalEN Need your suggestions Ruth! Please advice me!
Talk to them about number of columns and granularity, that should set you on track ;)
@@CurbalEN Yeah , Scratching my mind and also the major challenge is we don't have dates in the database and they want to see MTD and YTD as well..
Also look into aggregated tables, it should help, but if you dont have dates....
One question about this.. in which scenario we can do this ??
Anywhere you need to copy measures.
@@CurbalEN theses measures will work only for this pbix file only right .. if we want to use these measures the. We need to change the table names and field names right.. please correct me if am wrong
It depends on what you are trying to do. Sometimes you will have to.
I don't have DataModelSchema in my .pbix, only DataModel, I open it and it is encrypted, here an extract:
ì=”“œöSÔ£“Áå¢0s#"™…,‹¹óWxFÁ
Why is that? :S it this DataModelSchema the same as a .bim file in a SSAS Tabular Model? Would be awesome to just copy-paste it instead of migrating all to SSAS manually.
Don't skip any step and you will see what is wrong 😉
@@CurbalEN :O template
You got it 😉
4:08 PBI must have a right-click "duplicate measure" option.
I knowwww 😭😭😭
Tabular Editor has it...
Yes, but not everybody can install it.
which computer you are using.. because your none of the videos works. not even single one
Just a normal windows machine :)
what should i do with LineageTag
"measures": [
{
"name": "Total Hours",
"expression": "CALCULATE(sum(SelfService_Plan_DailyPlanHoursResourceVersion[ApprovedStdHours]))",
"lineageTag": "30f8c1b1-6ef4-456a-b1fe-63a310aca7a7",
"annotations": [
{
"name": "PBI_FormatHint",
"value": "{\"isGeneralNumber\":true}"
}
]
}
not working for some reason