Retrieving viz DAX

[smpa01]

@dm-p @d_gosbell @GilbertQ @AlexisOlson @gbrueckl

I am querying a view (table viz) from a power bi semantic model published in a workspace with premium capacity. The model is developed by a separate team and the view is deployed in the workspace by them. Let's call it model 1 and viz1.

I have access to that model and I am utilizing the DAX query that powers that viz (through Perf Analyzer manually) to be utilized in a different downstream model(dataset) developed and utilized by me.

Now. viz1 has a lot of implicit measure. So measure1 today is implicit sum(table[field1]). But tomorrow for some reason, it could be changed to implicit max(table[field1]). To be able to change, the implicit measures to explicit measures or even asking the other team to change implicit to explicit is out of my current scope.

Since I am directly utilizing the DAX query that powers viz1, is there any way for me to dynamically retrieve the dax that powers the viz1 of model1 (through web service or any other method) rather than manually checking everyday through Perf Analyzer? So that, I can ensure that I am utilizing the same DAX for downstream reports that is secured from any sudden change of any implicit measure for viz1.

Ideally, I would like to able to utilize a web service to query viz1 in page1/tabl1 of report1 of dataset1 of workspace1 to get the DAX that powers viz1?

Thank you in advance.

[AlexisOlson]

I believe this is probably possible. I'm not sure exactly how, but I know Measure Killer uses APIs to read report definitions in the Power BI Service, which include visual definitions.

For the visual above, the JSON definition when stored in the PBIP format looks like this under the new PBIR format.

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/1.0.0/schema.json",
  "name": "70256f0a34f1cbdabdaa",
  "position": {
    "x": 572.21995926680245,
    "y": 194.86761710794298,
    "z": 0,
    "width": 458.16700610997964,
    "height": 206.59877800407332,
    "tabOrder": 0
  },
  "visual": {
    "visualType": "tableEx",
    "query": {
      "queryState": {
        "Values": {
          "projections": [
            {
              "field": {
                "Column": {
                  "Expression": {
                    "SourceRef": {
                      "Entity": "Table1"
                    }
                  },
                  "Property": "Column1"
                }
              },
              "queryRef": "Table1.Column1",
              "nativeQueryRef": "Column1"
            },
            {
              "field": {
                "Aggregation": {
                  "Expression": {
                    "Column": {
                      "Expression": {
                        "SourceRef": {
                          "Entity": "Table1"
                        }
                      },
                      "Property": "Column1"
                    }
                  },
                  "Function": 3
                }
              },
              "queryRef": "Min(Table1.Column1)",
              "nativeQueryRef": "First Column1"
            },
            {
              "field": {
                "Aggregation": {
                  "Expression": {
                    "Column": {
                      "Expression": {
                        "SourceRef": {
                          "Entity": "Table1"
                        }
                      },
                      "Property": "Column1"
                    }
                  },
                  "Function": 2
                }
              },
              "queryRef": "Count(Table1.Column1)",
              "nativeQueryRef": "Count of Column1"
            },
            {
              "field": {
                "Measure": {
                  "Expression": {
                    "SourceRef": {
                      "Entity": "Table1"
                    }
                  },
                  "Property": "Measure1"
                }
              },
              "queryRef": "Table1.Measure1",
              "nativeQueryRef": "Measure1"
            }
          ]
        }
      }
    },
    "drillFilterOtherVisuals": true
  }
}

 You can read the column definitions from this format, at least in principle.

[smpa01]

@AlexisOlson  many thanks for this. This looks promising and I will to take it out on a spin

[AlexisOlson]

I don't have an answer but I'm curious about your use case. What is the nature of your downstream model? Why must it depend on definitions of implicit measure configurations in a visual?

[smpa01]

@AlexisOlson I am building a data-model called model2, needs to query the viz1 in model1 which essentially becomes the datasource (let's call it src1Model2) for model2 that I am developing.

model2 has additional datasource (dim and fact) that needs to be data-modelled with src1Model2 table according to the business req/ stakeholder req downstream consumption (completely separate from downstrream consumption req of model1).

Model1 and subsequently viz1 is developed by different team and they are using implicit measure in viz1 which I can't influence and/control. So if they had originally used an explicit measure called  tradeLoss = SUM(table[tradeLoss]) and changed to tradeLoss = MAX(table[tradeLoss]) , it would have not have made any difference to my query, cause on both days I am querying the viz like this and whether tradeLoss is SUM today, MAX tomorrow, MIN the day after, it does not put src1Model2 datasource at risk and the data I am querying would always be same as viz (i.e. src1Model2= viz1)

SUMMARIZE(dim1[field1],dim2[field2],"loss",[tradeLoss])

 However, if implicit measure is used, the engine changes the background query based on the implicit calculation that gets changed, so if implict SUM is used today then engine generates

SUMMARIZE(dim1[field1],dim2[field2],"loss",calculate(sum(table[tradeLoss]

 if implict MAX is used tomorrow then engine generates

SUMMARIZE(dim1[field1],dim2[field2],"loss",calculate(max(table[tradeLoss]

 which means I need to manually check if the SUM from yesterday got changed to MAX today. IF yes, I need to manually account for that change in the SSAS query that updates/refreshes src1Model2 adatsource.

Think of this problem, as retrieving data from World Bank. Imagine WB is using data from different sources (fact and dim) to generate a view of GDP by country. I need to write a query to get daily GDP data to be utilized in model2. Now, WB provides web service through REST API that enables me to query daily GDP data. Example

https://search.worldbank.org/api/v2/wds?format=xml&count_exact=Algeria&fl=count,volnb,totvolnb,docna,repnme

But if WB had used Power BI to generate GDP view, I would have required the same DAX query that powers the GDP viz to give me the daily GDP data unless there is web service (API) currently provided by POWER BI that returns me the same data in viz1 in page1/tabl1 of report1 of dataset1 of workspace1 without requiring to know the back ground dax.

//pseudo API
https:app.powerbi.com/groups/{groupID}? format=xml&page=1&viz=table

[AlexisOlson]

I understand your requirement, just not why it's a requirement.

If you're using something as a data source, it shouldn't be changing unpredictably. Having MAX instead of SUM is an important change unless it's just iterating over a single row where the result is the same (in which case, it shouldn't make a difference downstream).

What are you doing downstream that having a dynamic data source like that seems like a good option?

[smpa01]

@AlexisOlson  I admit, it is not ideal. I usually work with data coming from proper database. But for this project, I have been tasked to get the data coming from viz1 as one of the data sources for all downsteam reporting for my portion. 

So, before I turn this into a XY problem with the makers of model1 and start a storm, I am simply trying to figure out if there is a way (through web sevice or any other method) to sneak into the backdoor and get the DAX.