Is there a way to let users change a variable via the visualisation pane?

[SingularitySong]

I have a list of projects, each with a start_date and an end_date. I have created a table of months, where I can count how many projects were "in progress" during a month - any project where the start date is less than or equal to the last date of the month and the end date is greater than or equal to the first date of the month. The resulting count works well.

The problem is, however, each project has a Location_ID. I would like users on my report to be able to select a certain Location_ID and see only the number of projects in progress for that location, just like a slicer. But slicers don't work, because the count is calculated in the column already. I'm wondering if it would be possible to put a variable in my count, something like

filter(query1, Location_ID=Var1)

and then add a button or similar on my visualisation, allowing the user to change the variable. If they click Location1, it will only count projects with a Location_ID of Location1. If they click Location2, it will only count projects with a Location_ID of Location2, and so on. However, I can't find any way to link buttons to variables. I don't suppose anyone knows a way I could do this, or can give me a definitive "it's not possible"? If so, I may just have to create a selection for each location, and use the buttons to navigate between them, but this would be a lot of work. Thanks for your help.

[SingularitySong · Accepted solution]

Hey there, just in case anyone comes across this in the future, I figured out a solution. I was using an excel spreadsheet to provide the list of month start dates and end dates. All I needed to do was add a new column for Location_ID (call it Location_ID_2), and then copy and paste the whole list of months once for each different Location_ID, filling all cells up with the same Location_ID. Then once I refreshed the data on Power BI, I could simply filter my results by Filter(Query1, Query1.Location_ID = Location_ID_2), and it worked perfectly. If you're having similar problems, I suggest you try this.

[v-robertq-msft]

Hi, 

According to your description, I can roughly understand your requirement, you use the calculated column to get the Count value and want to filter it in the visual to be displayed in the report?

I think you can simply use the Maximum summarize type in the table chart to achieve this:

If you don’t want to use a What-id parameter to use the measure to filter the visual data, you can create a calculated table to link to the main table to filter the data:

This is my test data and Count column:

Count = COUNTX(FILTER(ALL('Table'),'Table'[Location_ID]=EARLIER('Table'[Location_ID])),'Table'[Project_ID])

Create a calculated table like this:

Slicer table = SUMMARIZE('Table','Table'[Location_ID])

Then link the table:

Then create a table chart to set the count as Maximum and a slicer to place the column in the slcier table, and you can get what you want:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

Here is one way to do this:
Example data:

Parameter:

(Whole number 1-20)
Measure using this:

Filtered = countrows(FILTER('Datediff','Datediff'[Case]=FilterParameter[FilterParameter Value]))

End result:
While I have something selected returns rowcount else blank

I hope this helps to solve your issue and if it does consider accepting this as a solution!

[SingularitySong]

Hi there, thanks for the quick and detailed reply. I've tried this out,  adding a location number  and a parameter but for some reason the filter doesn't pick up anything. All I get is a value of 0, no matter which parameter I select. Do you have any idea what's causing this?

[ValtteriN]

Hi,

My first instinct would be to check if the data types match each other. In my example the [Case] was a whole number. If your ID is a string that can't be converted to int things will become complicated.

[SingularitySong]

Ah, I think I see the problem. It took me a lot of puzzling to learn this, but parameters only work on measures, and I was trying to apply it to a column. Unfortunately I'm pretty sure that a measure just won't work for this. I need a column, as I'm calculating the data using my months table. Trying to calculate it as a measure gives me a "A single value for column x in table y cannot be determined. This can happen when a measure formular refers to a column that contains many values".

With this in mind, do you know if there's any way to apply parameters to a column?

[ValtteriN]

Okay, a new idea then. Since you are interested in a "in progess" projects in a location I propose the following: first use location a normal slicer then create filter column for in progress status. E.g. If('Project'[Status]="In progress",1,0) The you can use this slicer in combination with the location to get the count. Some sort of custom slicer ought to be the proper solution since the measure approach wasn't appropiate here. 

Of course one solution would be to transfer the "In progress" calculation to a measure. The a mere location filter should solve the issue. E.g. Calculate(count('Project'[ID],filter('Project','Project'[Status]="In progress")))

[SingularitySong]

Thanks for the suggestion. Unfortunately I don't think this would be suitable. I'd like to be able to see how many projects were active in past months, and an in-progress tag would only tell me which projects are active currently. Thanks for all your help, I'm sorry I keep rejecting the solutions. 😛