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 all, I've seen some variations on setting defaults on slicers but not found a way to do what I want specifically. I have a report to show licences being used over months and have 2 charts which show 16 months worth of use. Under the charts I have some card visuals showing assigned/used. When users click the months on the charts the values in the cards are filtered to only the selected month(s) however when the report loads and the user has not clicked a specific month on the charts the cards show the total amount over the whole 16 months. Is it possible to make the cards show data filtered to the current month if nothing is selected in the charts and then change to the selected month(s) if the chart is clicked?
I've tried measures with an if statement to check if the MonthYear (used in chart) is selected and calculate differently (filtered to current month) if not - This didn't work. Here's a screenshot of the report and one of the card measures:
I'm a n00b when it comes to PBI so any suggestions or links would be appreciated.
Thanks,
Scott
Solved! Go to Solution.
Hi @amitchandak
Thanks for your suggestions. One of my collagues helped with this one. I ended up using the following:
CountInsightsAssigned =
IF(ISFILTERED(All_Activity[MonthYear]),
CALCULATE(
COUNTA('All_Activity'[MonthYear]),
'All_Activity'[Active] IN { "Unused", "Used" },
'All_Activity'[Licence Type] IN { "Sales Insights + LinkedIn" }
),
var MonthMax = MAX(All_Activity[Month])
RETURN
CALCULATE(
COUNTA('All_Activity'[MonthYear]),
'All_Activity'[Active] IN { "Unused", "Used" },
'All_Activity'[Licence Type] IN { "Sales Insights + LinkedIn" },
'All_Activity'[Month] = MonthMax
)
)
I tried a variation of this previously but was unable to get it to work until I used the variable (or another column to get the max date form "Month" worked too).
Hi @amitchandak
Thanks for your suggestions. One of my collagues helped with this one. I ended up using the following:
CountInsightsAssigned =
IF(ISFILTERED(All_Activity[MonthYear]),
CALCULATE(
COUNTA('All_Activity'[MonthYear]),
'All_Activity'[Active] IN { "Unused", "Used" },
'All_Activity'[Licence Type] IN { "Sales Insights + LinkedIn" }
),
var MonthMax = MAX(All_Activity[Month])
RETURN
CALCULATE(
COUNTA('All_Activity'[MonthYear]),
'All_Activity'[Active] IN { "Unused", "Used" },
'All_Activity'[Licence Type] IN { "Sales Insights + LinkedIn" },
'All_Activity'[Month] = MonthMax
)
)
I tried a variation of this previously but was unable to get it to work until I used the variable (or another column to get the max date form "Month" worked too).
@loracsgray , based on what I got. see if this can help
MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |