Power BI Blog: Parameter Typo

Welcome back to this week’s edition of the Power BI blog series. This week, we look at an issue which can occur when using a parameter as a filter.

Consider the following measure:

We have created a simple measure.

This measure is summing the Amount field on a table called Actuals, subject to the associated Account field being filtered for values equal to  “1000”.  You may wonder what this has to do with parameters, but stay with us, and it will become clear!

Notice that the Format is set to General and the decimal places are set to Auto. Power BI recognises that our data includes two [2] decimal places, so the measure would be displayed accordingly when we choose to create a Matrix visualisation:

If we had a parameter of data type text set to a value of “1000” (in this case P_SalesCode), the result would be the same:

In the next measure Sales Wrong Parameter Type, we have deliberately omitted the speech marks around 1000. This has the same effect as using a parameter with a numerical data type:

Power BI does not issue an error, it just assumes that we only want the numeric values from the Account field and sets the format to Whole Number and the decimal places to zero [0] accordingly. It is not until it comes to display the measure, that an error is revealed:

The data type mismatch has been picked up and we need to correct it. We can go back and add the speech marks to the filter, but the format doesn’t change:

This will not be a suitable format as the decimal places are not displayed. There are two ways to remedy this. We can delete the measure and enter it again or we can adjust the Format.

The measure is then displayed correctly. This is something to watch out for when we are using parameters as filters.

Check back next week for more Power BI tips and tricks!