Power Apps Integration - Calling column values with PowerBIIntegration.Data

[harry1848]

Description: 

I have a Power BI report with a Power Apps integrated visual (create new NOT use existing). On the first page of the report, I have a series of filters and slicers that reduce my data displayed in a table to the Project Number and Task Numbers that are also displayed in a gallery screen in the Power Apps visual on page 2. Using a navigation button, these gallery selections are passed along to a form on screen 2 of the app, which displays the information related to Project Numbers and Task Numbers (Like Project owner, project date range, task number date range, etc.).
Here is the problem: In order to prevent users from submitting a form with values and date ranges that are outside the useful range, I need to do some sort of validation on values (filtered in the report, displayed in gallery on screen 1 and passed along to the form on screen 2). To do a validation, I need to compare the dates of  values selected, against all the values from the project date range column, to make sure the form request is not too old, or too far into the future with values associated with planned work. 
When I write the [@PowerBIIntegration].Data.'ColumnName' the values displayed are only the values available from the filtered report page (which also matched the values in gallery of screen 1). I need to access the entire column of [@PowerBIIntegration].Data.'ColumnName' instead of only the values it passes along. 

Work Arounds Tried: The first solution is to create another connection to the data source, in this case a SQL Server Analysis Services Database. Unfortunately this is not an option. The data is available through a Power BI report with a Live connection, and corporate policy is to not allow other connections to this server.

Second Option; Because the report has a live connection, I can't create a second query that duplicates the data I need, thus allowing me to call the columns under a different name, instead of referring to the filtered column results. 

Third Option Question: Is there some function I can write that removes the filtered results of [@PowerBIIntegration].Data.'ColumnName' so that it gets all the values of the column instead of the filtered results?

Thanks!

[harry1848]

"then what's the point of passing the data along (or using Power Bi at all)? You users may not understand  what is happening." -What do you mean? I need to find some values and use those to submit a form. The data model we are working with is enourmous, and is stored in a SQL Server Analysis Services Database. I have not been able to connect directly to this in Power Apps, nor can I build a dataverse table via dataflow, which would enable me to use a model driven app instead of this current setup which is the Power App dataset-> Power App report visual ->Canvas App form submission. 

The only way to use this data and submit a form(in this case its called a "project cost transfer request") is to start with a power BI report and use a canvas app imbedded. The passing of data with PowerBIIntegration.Data is the only way any values get into the canvas app controls (gallery or form). , and as I understand it is the limit of the canvas app integration technology. 

[lbendlin]

I have not been able to connect directly to this in Power Apps

Can you  please elaborate?  What have you tried and where are you stuck?

[harry1848]

So in the canvas app editor, it is possible to add a new data source, and there is an option to connect to a sql server. I have the server details, and the admin assures me that this database is shared with our entire organization, and I can confirm connecting to it in a fresh Power BI report in PBI desktop. With admin assistance, we tried connecting and get a 

So I thought, surely I could use a fresh dataflow in Power Apps, since dataflows offer a connection with SQL Server Analysis Services. I spoke with an admin, and the connection to this database was shared with our defualt power apps environment. But when I click on it, the next screen is blank with a progress wheel that spins on and on forever. 

[lbendlin · Accepted solution]

You might be mixing a couple of slightly incompatible concepts. SQL Server Analysis Services are multidimensional cubes - they are not databases. Dataflows are flat csv (well, Parquet) files. If you want to connect to SSAS from PowerApps you will have to run a MDX query that pulls the required data into a flat format that you can then use into a gallery.

[harry1848]

Thank you, you are correct, it seems I was confusing those concepts. 

[lbendlin]

I need to access the entire column of [@PowerBIIntegration].Data.'ColumnName' instead of only the values it passes along

then what's the point of passing the data along (or using Power Bi at all)? You users may not understand  what is happening.