Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
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]) )
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
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')
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |