Search code examples
reportpowerpivotpowerbidax

How to pass parameters to measures in Power BI?


I'm new to Power BI and here's the deal:

I have the following query which calculates a measure:

MyMeasure = CALCULATE(COUNTA(F_incident[INCIDENT_ID]);F_incident[OPEN_TIME]>DATE(2016;1;1))

I need the date to be replaced by a parameter @param, so that external users could enter custom dates causing the measure to recalculate.

Is this possible in Power BI?


Solution

  • In your situation you are looking for an end-user to enter a date. That date will then be used in a measure to show you the number of incidents since that date (but not including that date).

    I would recommend, as mentioned in the comments, a regular date table related to your F_Incident table that you could then use with a regular date slicer. While a regular date slicer requires a range rather than a single date, it is a lot more flexible for the end-user. Power BI's built-in slicer handles dates quite well. E.g. the relative date slicer allows an end-user to quickly pick "last month" as an option. See: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-feature-summary/#reportView


    If you've genuinely ruled out a regular date table for some reason, then another solution for a measure that responds to user input is to create a disconnected parameter table with options for the user to choose from (typically via a slicer). More information here: http://www.daxpatterns.com/parameter-table/

    This parameter table can certainly be a date table. Because the table isn't related to any other table, it doesn't automatically filter anything. However, it can be referenced in measures such as you describe in your question. (I would recommend doing more error checking in your measure for situations such as nothing being selected, or multiple dates being selected.)

    Once you have a parameter table set up, you can also pass in the filter information by URL. More information here: https://powerbi.microsoft.com/en-us/documentation/powerbi-service-url-filters/. Note that you can't pass a date directly via URL, but if you add a text-field version of the date in your parameter table, you can filter on that to the same effect. Note, however, that it's more common to put a slicer for the parameter value right on the report rather than passing it in via URL.