Forum Discussion
Measure based on a multi-select slicer
Hi,
I'm trying to create a measure that obtains a count of records based on the records listed in the relevant months from the slicer selection.
I can get the list of months using
Multi_Selected_Months = CONCATENATEX ( VALUES ( TaskRange[Month] ), [Month], ",")But when I use following or try different filters, I can't get the results I',m after
TotalTasks =
Return
COUNTROWS(
FILTER(
All(TaskRange),
TaskRange[Month]=[Multi_Selected_Months])
)Someone might select january, february, may, august and I only need to see the totals for the months selected.
If I do a datebetween the first and last date selected this is fine
Any help appreciated
Thanks
Thanks all,
Managed to resolve it using the month table with the count of measure entries, I created another card that just used this and summed the values up for the selected months
5 Replies
Set up a proper date table and link that to your TaskRange table. Use columns from the Date table on your visuals, including the slicer, and your measure becomes simply
Total tasks = COUNTROWS( 'TaskRange')The table I'm using already has a date range on it as it would appear in a seperate table, and is fine for all my other visuals as they are filtered on categories.
I'm creating another visual multi-card that contains a total count of all tasks within the range selected no matter what category they are and then another measure gets the total for the category to then do a percentage.
The measure based on the category within the range is correct, it's the overall I'm having an issue with
I've created a table for the dates to choose (to confirm it has no bearing on the issue), along with the totals for each month
AllMonths = Distinct ( TaskRange[Month])Total = COUNTROWS(FILTER(ALL(TaskRange),TaskRange[Month] = AllMonths[Month]))Month Total 1/1/2022 55 1/2/2022 145 1/3/2022 543 1/1/2023 7 1/10/2021 15 1/11/2022 325 The user can select mutliple dates from the range using a splicer and not in sequential format, so may choose 1/1/2022, 1/3/2022, 1/11/2022
I need to be able to add the totals for each month to get an overall figure which should be 55 + 543 + 321 = 919
But what happens is that I get blank as it only works for one month, i can't use other formulas as it seems to add everything that occurs between the min and max date
You could use TREATAS with your date slicer table
Total = CALCULATE( COUNTROWS(TaskRange), TREATAS( VALUES('Month Slicer'[Month]), TaskRange[Month]) )
Thanks all,
Managed to resolve it using the month table with the count of measure entries, I created another card that just used this and summed the values up for the selected months
