Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
We have replicated all Tables/Columns on our SQL Server, with same naming convention over in Snowflake. Now, its time to move the Calculated Measures and Calculated Columns within each report.
So far, on my first few runs... I've loaded snowflake tables onto an existing report, and used the old trick of changing the home table for Calculated Measures, along with pointing to the identical column within the Snowflake Table. That's okay for Calculated Measures... However, this is very time consuming, as I have too measures many to count.
Further, when it comes to the Calculated Columns - I have no tricks.
I tried to get fancy, and copy the snowflake source/navigation into the original table on the Query Editor, in hopes evertything just falls in place (since the naming convention is the same), while maintaining the calculated Columns/Measures... but Im getting errors of: "The columns [...] either doesn't exist or doesn't have a relationship to any table available in the current context." -or- "Column [...] in table [...] cannot be found or may not be used in this expression."
Therefore, my question is, has anyone figured out a more streamlined way of moving Calculated Columns (and Measures) from SQL Server Tables to Snowflake Tables??
Solved! Go to Solution.
Ideally this is what you do. 0% work will need to be done in Power BI, all in Power Query.
Power BI Desktop will not know the difference. It will just work. Wash, rinse, repeat for every table.
In the tabular model, the table names aren't used. It really references table numbers, which is why you can easily rename tables with no harm. But it means you cannot create a new query in Power query with the same name and delete the old query and then import. They will have different underlying IDs.
This article from P3 is essentially the same thing.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIdeally this is what you do. 0% work will need to be done in Power BI, all in Power Query.
Power BI Desktop will not know the difference. It will just work. Wash, rinse, repeat for every table.
In the tabular model, the table names aren't used. It really references table numbers, which is why you can easily rename tables with no harm. But it means you cannot create a new query in Power query with the same name and delete the old query and then import. They will have different underlying IDs.
This article from P3 is essentially the same thing.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans ,
Totally cool! This will be a huge time saver for me.
- I am experiecing other little bugs on close and apply, for two PBI Tables that were actually developed via SQL Queries. Here's the error prompt:
So it appears my next hurdle will be to point all of my SQL Queirnes to the Snowflake Tables; I'm guessing.
Those look like calculated columns issues given the error. Is that what they are? Calculated Columns can cause circular dependency issues. If that is not the case, provide more details.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou can do one of two things:
I personally recommend option 1.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad to assist @qbarnes. Hope your project moves forward smoothly.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.