Power BI Desktop to Report Builder

[amandabus21]

Hello, 

Im trying to get some of my Power BI Desktop into a Paginated Power BI Report Builder file.

I used my Power BI service Dataset Connection along with the Performance Analyzer DAX method from the Desktop.

However, when my data went in thorugh Query Designer they, all went in as "First." (Picture below) This is an issue because I need all the entries not just the 1st. 

Is there a way can change it so its just the field and when I set the Date Parameter it brings me back all the entries for that day and not the 1st entry that exisit from the database.

DAX

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('data PropertyDamage'[AccidentDescription])),
NOT(ISBLANK('data PropertyDamage'[AccidentDescription]))
)

VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'data PropertyDamage'[ID],
'data TOSAIncident'[ID],
'data TOSAIncident'[OccurrenceTimeStamp],
'v_CombinedLocations'[LocationDescription],
'def TrolleyLines'[LineName],
'def TrolleyDirections'[Direction],
'data TOSAIncident'[Operator],
'data PropertyDamage'[AccidentDescription],
'data PropertyDamage'[SDTIDamage],
'data PropertyDamage'[NonSDTIDamage],
'data TOSAIncident'[ReportedBy],
'data TOSAIncident'[ReportedByPosition],
'v_AffCarsByInc_ActionIDAll0'[Cars],
'data TOSAIncident'[TrainNumber],
'data PropertyDamage'[TimeStamp],
'data TOSAIncident'[Correct Date],
__DS0FilterTable,
"Line_Color_Code", IGNORE('def TrolleyLines'[Line Color Code]),
"CountRowsdata_PropertyDamage", CALCULATE(COUNTROWS('data PropertyDamage')),
"CountRowsdata_TOSAIncident", CALCULATE(COUNTROWS('data TOSAIncident')),
"CountRowsv_AffCarsByInc_ActionIDAll0", CALCULATE(COUNTROWS('v_AffCarsByInc_ActionIDAll0'))
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('data PropertyDamage'[ID])),
NOT(ISBLANK('data TOSAIncident'[ID]))
),
NOT(ISBLANK('data TOSAIncident'[OccurrenceTimeStamp]))
),
NOT(ISBLANK('v_CombinedLocations'[LocationDescription]))
),
NOT(ISBLANK('def TrolleyLines'[LineName]))
),
NOT(ISBLANK('def TrolleyDirections'[Direction]))
),
NOT(ISBLANK('data TOSAIncident'[Operator]))
),
NOT(ISBLANK('data PropertyDamage'[AccidentDescription]))
),
NOT(ISBLANK('data PropertyDamage'[SDTIDamage]))
),
NOT(ISBLANK('data PropertyDamage'[NonSDTIDamage]))
),
NOT(ISBLANK('data TOSAIncident'[ReportedBy]))
),
NOT(ISBLANK('data TOSAIncident'[ReportedByPosition]))
),
NOT(ISBLANK('v_AffCarsByInc_ActionIDAll0'[Cars]))
),
NOT(ISBLANK('data TOSAIncident'[TrainNumber]))
),
NOT(ISBLANK('data PropertyDamage'[TimeStamp]))
),
NOT(ISBLANK('data TOSAIncident'[Correct Date]))
)
)
),
"'data PropertyDamage'[ID]", 'data PropertyDamage'[ID],
"'data TOSAIncident'[ID]", 'data TOSAIncident'[ID],
"'data TOSAIncident'[OccurrenceTimeStamp]", 'data TOSAIncident'[OccurrenceTimeStamp],
"'v_CombinedLocations'[LocationDescription]", 'v_CombinedLocations'[LocationDescription],
"'def TrolleyLines'[LineName]", 'def TrolleyLines'[LineName],
"'def TrolleyDirections'[Direction]", 'def TrolleyDirections'[Direction],
"'data TOSAIncident'[Operator]", 'data TOSAIncident'[Operator],
"'data PropertyDamage'[AccidentDescription]", 'data PropertyDamage'[AccidentDescription],
"'data PropertyDamage'[SDTIDamage]", 'data PropertyDamage'[SDTIDamage],
"'data PropertyDamage'[NonSDTIDamage]", 'data PropertyDamage'[NonSDTIDamage],
"'data TOSAIncident'[ReportedBy]", 'data TOSAIncident'[ReportedBy],
"'data TOSAIncident'[ReportedByPosition]", 'data TOSAIncident'[ReportedByPosition],
"'v_AffCarsByInc_ActionIDAll0'[Cars]", 'v_AffCarsByInc_ActionIDAll0'[Cars],
"'data TOSAIncident'[TrainNumber]", 'data TOSAIncident'[TrainNumber],
"'data PropertyDamage'[TimeStamp]", 'data PropertyDamage'[TimeStamp],
"'data TOSAIncident'[Correct Date]", 'data TOSAIncident'[Correct Date],
"Line_Color_Code", [Line_Color_Code]
)

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'data PropertyDamage'[ID],
1,
'data TOSAIncident'[ID],
1,
'data TOSAIncident'[OccurrenceTimeStamp],
1,
'v_CombinedLocations'[LocationDescription],
1,
'def TrolleyLines'[LineName],
1,
'def TrolleyDirections'[Direction],
1,
'data TOSAIncident'[Operator],
1,
'data PropertyDamage'[AccidentDescription],
1,
'data PropertyDamage'[SDTIDamage],
1,
'data PropertyDamage'[NonSDTIDamage],
1,
'data TOSAIncident'[ReportedBy],
1,
'data TOSAIncident'[ReportedByPosition],
1,
'v_AffCarsByInc_ActionIDAll0'[Cars],
1,
'data TOSAIncident'[TrainNumber],
1,
'data PropertyDamage'[TimeStamp],
1,
'data TOSAIncident'[Correct Date],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'data PropertyDamage'[ID],
'data TOSAIncident'[ID],
'data TOSAIncident'[OccurrenceTimeStamp],
'v_CombinedLocations'[LocationDescription],
'def TrolleyLines'[LineName],
'def TrolleyDirections'[Direction],
'data TOSAIncident'[Operator],
'data PropertyDamage'[AccidentDescription],
'data PropertyDamage'[SDTIDamage],
'data PropertyDamage'[NonSDTIDamage],
'data TOSAIncident'[ReportedBy],
'data TOSAIncident'[ReportedByPosition],
'v_AffCarsByInc_ActionIDAll0'[Cars],
'data TOSAIncident'[TrainNumber],
'data PropertyDamage'[TimeStamp],
'data TOSAIncident'[Correct Date]

[v-yiruan-msft]

Hi @amandabus21 ,

Please don't paste the above query into Expression area directly, you can follow the steps in the following official documentation to do it.

Create the dataset with the query

1. Right-click the dataset under Data Sources and select Add Dataset

2. In Dataset Properties, give it a name, and select Query Designer.

3. Make sure DAX is selected, and deselect the Design Mode icon. In the upper box, paste the above DAX Query in your post.

Best Regards

[amandabus21]

hi @v-yiruan-msft 

When I do it this way, and I run the report, it stays loading for a very long time and never actually provides a result.

[v-yiruan-msft]

Hi  @amandabus21 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[v-yiruan-msft]

Hi @amandabus21 ,

Please check if you hit any following limitation, you can find more details in this official documentation...

If the above one is OK, could you please provide the following information for further troubleshooting?

1. Whether the above DAX query can load the data in Power BI Desktop? Is it large?

2. Provide the screenshot of your Performance Analyzer with the detailed steps?

3. Share some raw data of involved tables in the above DAX query and backend logic 

Best Regards