Forum Discussion
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
- amitchandak
Super User
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])))
- mrdepsRegular 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.