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!
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!
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.
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?
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.
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.
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?
@@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!
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
@@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.
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!
@@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.
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?
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 !
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)
@@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
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!
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
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.
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.
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
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!
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!
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!
OMG!!!! I LOVE YOU!!!!!! You have made our lives soooo much easier!
Did I say I love you?
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 :)
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.
What if I just want to add in the measure to a different report? Can I pick and choose?
"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!!
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?
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?
you are a gem
You saved me. Thanks a lot 😊
At your service :)
Can we change columns dax?
Thanks for such a Marvelous Trick
Thank you so much for the tip. It helped to create more than 300 measures without using external tools :)
🥳🥳
Could you please make a video on how to copy measures in bulk from one pbix file to another pbix file?
Why do you save it as a pbit file instead of a pbix file? And can this be done with pbix files?
PERFECT* thanks Ruth
❄❄
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.
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?
Thanks Ruth for sharing your ideas
Always my pleasure :)
If we change the measures from one table name to new table name then the table names in the measures changes ?
Interesante, ¡Gracias Ruth!
Un placer!
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
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 👏
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!
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
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?
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!
Excelent Ruth!
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....
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!
I get an error, the JSON input is not properly formed
same. @curbal any idea why this is?
This is crazily nice!!! :D :D
🥳🥳
Realy great! Thank you
🥳🥳
3:10 Nice trick!!!!
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?
Hi Ruth, what software do you use to record and edit your videos?
Camtasia
@@CurbalEN Eres un amor! =)
Does anyone have a similar solution for powerbi Fabric
Well this was a nice trick, but this is same as changing the home table of all the measures, which can be done with the pbix file with in 2-5 secs.
4:08 PBI must have a right-click "duplicate measure" option.
I knowwww 😭😭😭
Tabular Editor has it...
Yes, but not everybody can install it.
Muito bom parabéns.
Obrigada :)
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.
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!
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.
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 😉
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