Forum Discussion

mrdeps's avatar
mrdeps
Regular Visitor
4 years ago

Variable for Power BI measure

Good morning experts,

It’s my first post, I’m a new Power BI Developer. I want to ask you help for a measure. Just to explain you my activity:

The fields in my table are: 'WorkflowId', 'WorkflowRunId' (is an ID that indicate the refresh of WorkflowId. Each WorkflowId could have many WorkflowRunId), 'FlagisError' (indicate if the WorkflowRunId is success(FlagisError=0) or failed(FlagisError=1)) and 'IndexCumulative' that I create before load data between this query (select *, ROW_NUMBER() OVER (PARTITION BY WorkflowId order by StartTime DESC) IndexCumulative from WorkflowRuns), for each WorkflowId make a count of row.

For example:

 

After I create these measure beacause I would know the percentage of failed workflowrun just for the last 50 WorkflowRunId for each WorkflowId:

- Last50FailedWorkflowRunIDs = CALCULATE(COUNT(WorkflowRuns[WorkflowRunId]),FILTER(WorkflowRuns,WorkflowRuns[FlagIsError]=1&&WorkflowRuns[IndexCumulative]<=50))

- Last50WorkflowRunIDs = CALCULATE(COUNT(WorkflowRuns[WorkflowRunId]),WorkflowRuns[IndexCumulative]<=50)

- %Last50RunsFailed = WorkflowRuns[Last50FailedWorkflowRunIDs]/WorkflowRuns[Last50WorkflowRunIDs]

Now I would like to make this value dynamic, instead 50, I would insert a variable that allow me to filter for a particular range. For example if, in the Filters section, I select 10

 

 

I would that the 3 measures above filter for 10 or for any value that I indicate in the filter.

I hope that my question is clear. Thanks a lot for your answers.

2 Replies

  • mrdeps , Try like , you can use what if parameter to make it dynamic

     

    Last50WorkflowRunIDs = Sumx(filter(Values(WorkflowRuns[Workflowrunid]), [IndexCumulative] <=50 ) , CALCULATE(COUNT(WorkflowRuns[WorkflowRunId])))

     

    or

     

    Last50WorkflowRunIDs = Sumx(filter(Values(WorkflowRuns[Workflowrunid]), [IndexCumulative] <=[what if meausre]) , CALCULATE(COUNT(WorkflowRuns[WorkflowRunId])))

    • mrdeps's avatar
      mrdeps
      Regular Visitor

      Hi amitchandak , thanks for your answer. I tried with your expression but it didn't work. When I use your first expression DAX give me an error 'Cannot find IndexCumulative':

      I don't know why I receive this error. But I find another way to use dynamic filter.