Looking up value in paginated report and dosen't show blank values.

[Anonymous]

Hi,

I have two same reports that one is BI dashboard publised from desktop app and the other one is paginated report.

They are exact same format data table and using same DAX data source such twins. 

They are linking 'Contract Amount' to 'Etax Amount' and they are located in different data table in DAX(with relationship).

The issue is BI dashboard is showing All the Etax Amount which don't have Contract Amount(shown as blank in snapshot in ascending order),

but paginated report only shows Etax Amounts which have Contract Amount meaning it take out all Etax Amount rows which don't have Contract Amount.

As snaphots, there is no filter on them.

It is not issue about DAX or Query data sourse.

Pls help.

*the reason creating paginated one is for power automate exporting data flow only. 

BI dashboard pulished

Paginated report

[v-prasare]

Hi @Anonymous,

We would like to confirm if our community members answer resolves your query or if you need further help could you please answer to our super user probing question?

@bchager ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[bchager]

@Anonymous  Why not use the "Run a query against a dataset" action in Power Automate instead of creating a paginated report just to extract data from it?

[Anonymous]

I tried but it was a bit difficult to get data in excel. Can this be fetch values as same as paginated report? 

[bchager]

@Anonymous  Yes it's very straightforward. Select the "Run a query against a dataset" action in Power Automate and connect it to a Power BI report published in the service. Once connected, run the Performance Analyzer in the desktop report or from the service on a page containing a table visualization for example, then copy the DAX from it and paste it into the Query text setion below. From there you can create HTML tables and CSV files.

[Anonymous]

+not PBI question but what next step do you recommend to get data in excel or csv format?

[bchager]

@Anonymous You can use a Select action from the First table rows from the Run a query against a dataset action, then the Create CSV table action using the output from the previous Select action, then the SharePoint Create file action using the output from the Create CSV table action. **Just be aware that when you get the DAX behind the Power BI table visualization using performance analyzer, the default TOPN value (max number of rows that will be extracted), is 501. So set it to a higher number if necessary.**

[Anonymous]

It looks nice and could be the better solution for me. 

I got this query and do you think it will be okay updating values automatically every week as per dataset? This analyzer names particular week number W38(i'm using auto week number title measure for the table as below) but if data sourse updated then it will become W39 accordingly? 

Title - TOSS e-tax total =

    "TOSS e-tax total_"

    & YEAR(TODAY())

    & "_W" & WEEKNUM(TODAY())

[bchager]

@Anonymous Yes, if you have that measure in a table visualization, you're using the DAX for that table in the Run a query against a dataset action, and you refresh the dataset before running the Flow, the CSV produced by the flow will reflect the updated data.

[Anonymous]

I've created below flow and got Result 1 but I need Result 2, how can I?

Also Result 2 in PBI view seems columns orders are different to original table in visual. 

[bchager]

@Anonymous See my previous response. You have to create the columns with the Select action first.

[Anonymous]

thanks, what is Map field? what should I put? 

and TOPN can I put 999,999? there will be more rows than 100,000 or any limitation? 

[bchager · Accepted solution]

@Anonymous In the Map section you name the fields in the first column, then click in the space in the second column, select the function icon, then key in expressions like I shared below. That will bring the Power BI data into your columns. As to the row limit, I'd suggest testing it. I'm not aware of any limits.

If you have any other Power Automate questions I suggest hitting up that forum.

Good luck!

[Anonymous]

I cannot find Actual transaction details in function field... 

[bchager]

@Anonymous That function retrieves the value from the field in the current record being processed. In my example, Actuals Transaction Detail is the Power BI table name and Month with Index is the field name.