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.
I have created a table visual that has columns from my original query, but also includes a measure that I want to be able to join back to my original query. Can I create a Data Table from a Table Visual?
Table Visual:
Item: From Query1
Store: From Query1
Week_Ending: From Query1
AvgPrice: sum(Query1[$Sales] )/sum(Query1[UnitSales]
RegularPrice = calculate(MAX(Query1[AvgPriceCol]),datesinperiod(Query1[Week_Ending],lastdate(Query1[Week_Ending]),-84,DAY))
*AvgPriceCol is a column from Query1 and is the same result as AvgPrice
I want to join RegularPrice back to Query1 and have the join based on Item, Store, and Week_Ending, however it's just a table visual right now...
Solved! Go to Solution.
If you want to make RegularPrice to be a calculated column in Query1 table, you can use following formula.
RegularPrice_Column = CALCULATE ( MAX ( Query1[AvgPriceCol] ), FILTER ( Query1, Query1[Item] = EARLIER ( Query1[Item] ) && Query1[Store] = EARLIER ( Query1[Store] ) ), DATESINPERIOD ( Query1[Week_Ending], LASTDATE ( Query1[Week_Ending] ), -84, DAY ) )
You can use R scripts to export data from power bi back to SQL, please refer to links below:
http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/ http://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server
*Note: R visuals in Power BI Desktop has a few limitations.
If you want to export visual report data to .csv file. You can select the ellipses in the top right corner of the visualization and choose the Export data icon.
*Note: 30,000 rows is the limitation for exporting visual report data to .csv file.
Regards,
If you want to make RegularPrice to be a calculated column in Query1 table, you can use following formula.
RegularPrice_Column = CALCULATE ( MAX ( Query1[AvgPriceCol] ), FILTER ( Query1, Query1[Item] = EARLIER ( Query1[Item] ) && Query1[Store] = EARLIER ( Query1[Store] ) ), DATESINPERIOD ( Query1[Week_Ending], LASTDATE ( Query1[Week_Ending] ), -84, DAY ) )
You can use R scripts to export data from power bi back to SQL, please refer to links below:
http://www.thebiccountant.com/2015/12/28/how-to-export-data-from-power-bi-and-power-query/ http://stackoverflow.com/questions/19190744/how-to-quickly-export-data-from-r-to-sql-server
*Note: R visuals in Power BI Desktop has a few limitations.
If you want to export visual report data to .csv file. You can select the ellipses in the top right corner of the visualization and choose the Export data icon.
*Note: 30,000 rows is the limitation for exporting visual report data to .csv file.
Regards,
Wow! This is amazing!! Thank you! I have 14 million rows. Works like a charm!
Covering 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.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |