Changing from parameter to excel file to update measure

[Anonymous]

Hello all, 

I have a couple of measures that search for and count records when they find certain words within a text field on those records. 

I was updating these search terms using parameters, however, when publishing to Power BI services, end users are not able to update these parameters and therefore use the report. 

I instead changed the source file of these parameters to an excel file, which i have saved on sharepoint and pulled into Power BI using the file location in the 'Web' data source. 

Here is the value of the parameter from this souce...

And this is the original formula using the built in parameters in Power BI desktop.

OR CN Mention Count = VAR s1 = MAX ( 'Text Search (Or1)'[Text Search (Or1)] )

VAR s2 = MAX ( 'Text Search (Or2)'[Text Search (Or2)])

VAR s3 = MAX ( 'Text Search (Or3)'[Text Search (Or3)])

RETURN COUNTROWS(CALCULATETABLE('All Casenotes',filter('All Casenotes',CONTAINSSTRING([Notes on session], s1 ) || CONTAINSSTRING( [Notes on session], s2 ) || CONTAINSSTRING( [Notes on session], s3 ) ) )

)

The problem is, when i change these parameters to the new parameters (using the text file as the data source), then the formulas either no longer work or only find a small fraction of the records that they should.  here is the updated formula. 

OR CN Mention Count = VAR s1 = MAX ( OR1[OR1] )

VAR s2 = MAX ( OR2[OR2])

VAR s3 = MAX ( OR3[OR3])

RETURN COUNTROWS(CALCULATETABLE('All Casenotes',filter('All Casenotes',CONTAINSSTRING([Notes on session], s1 ) || CONTAINSSTRING( [Notes on session], s2 ) || CONTAINSSTRING( [Notes on session], s3 ) ) )

)

I'm guessing this is because i don't understand something fundamental about how these two sources work?

[Anonymous]

Hi

The confusion here was actually because Power BI desktop needed to be resfreshed twice before the parameters updated in the report which was causing the confusion and mis-match

[Anonymous]

Hi Ibendlin, 

Sorry for the late reply. 

I cannot change only one parameter. I think PBI won't accept empty values in parameters. 

I instead changed all the parameters coming from the excel file to 'house' and used this within my formula which returned - 2772 rows. 

Then switched over to using the parameters within PBI (all set to "house") and found 2399 rows. 

Not sure what is happening here and why this word appears to work differently depending on how it is fed into the formula?

Thanks 

Euan

[lbendlin · Accepted solution]

"then the formulas either no longer work or only find a small fraction of the records that they should."

Can you prove that assertion?  Can you put just a single search term into the Excel file and then check if the report reacts as expected?