Bring a measure of the data model back into PQ

[LucaA]

Hi all,

i am using PQ and PP data model. I would like to reimport in PQ an allocation driver calculated by the data model. So I have in excel a standard table with some output calculated with "CUBEVALUE" formula and i would like to bring this table back to PQ to use them to do additional allocation (directly in PQ) and bring the results into the data model again. (i am not using Power Pivot to calculate the allocation driver because i should use several measure to cover all the options)

Is this possible or it creates a sort of "circular reference"? Is the whole data model able to update correctly recalculating the whole flow?

thanks

Luca

[mahoneypat]

What calculation are you doing on DAX/PowerPivot side?  The simplest approach would be to reproduce your calculation in your query. 

However, you can load your initial table from the query editor, add it to the local model, add your calculated column(s) with DAX, and then make a new query using that table as the source.  It is not a recommended practice but it can be done.

Pat

[dk_dk]

Hi @mahoneypat 

May be a silly question but how do I query a table with DAX columns as a source in the same pbix? What do I pick as data source for Query2?

So I have Query1 which I load, add an extra column with DAX, and then I want to get the entire thing back into PowerQuery as Query2, while staying in the same project. 

Also, I imagine there is some limitation to this - for instance if I would then use Query2 in an additional step in Query1, I presume that would be some sort of a circular reference error, right?

Thanks for your help!

[LucaA]

@mahoneypat i did not understand your solution.

However i am calculating allocation drivers. I could do them directly in PQ but it would require a lot of "Group by" functions and it slows down the model.

Moreover to do them as Measure in PP it would require to create a measure for each allocation driver for each period and they are too many

[v-kelly-msft]

Hi @LucaA ,

Is the PQ you mean in Excel or power bi?

Check whether below reference help:

https://excel.city/2018/11/filter-excel-function-cubevalue-connected-to-power-bi/

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!