Forum Discussion

ninsights's avatar
ninsights
Icon for Helper III rankHelper III
4 years ago
Solved

Writing Calculated Visualization Back to SQL Table

My Power BI file is published to the Power BI service. The data source is a SQL Server. I have the following visualization in my file where QtyOnHand is a CALCULATED field. I would like to write t...
  • smpa01's avatar
    4 years ago

    ninsights  if you have published this on a SSAS server (pbi workspace for example), you can write a PQ where you query the same SSAS server and utilize the same DAX query that generates  this viz  and try to write back the results in a SQL table. 

     

    An example of SQL write back with PQ

     

    let
      Source   = Sql.Database("myserver", "newtest"), 
      emailVal = "[email protected]", 
      Custom1  = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & emailVal & "')", 
      Custom2  = Value.NativeQuery(Source, Custom1)
    in
      Custom2

     Something like this

     

    let
    Source = Sql.Database("myserver", "newtest"),
    SSAS query = SSAS Q with DAX query,
    Val = convert the SSAS query to SQL insert row,
    Custom1 = "INSERT INTO [newtest].[dbo].[Table_1] (Email) VALUES('" & Val & "')", Custom2 = Value.NativeQuery(Source, Custom1)
    in Custom2