Hi everyone, I'm a relatively new Power BI user and have spent a lot of time trying to solve this puzzle (including reading a lot of these forums), but I keep coming up blank and would really appreciate some help.
My report looks at survey responses from the past three years, and I'm trying to look at how those survey responses compare to data taken at other intervals. There are three tables that are relevant here:
- the main 'responses' table, which has a row for each completed survey and includes a year column.
- an 'activity' table showing which activities each respondent was taking part in. It connects to the responses table in a many to one, both ways relationship based on the unique id assigned to each completed survey.
- a table containing the other data for comparison, which has rows for each year and rows for each activity (eg there's a row for 2024 activity 1, another for 2024 activity 2 etc., plus for each year there's a row for "all activities")
I want my measure to select the desired value based on the year and activity slicers that report users select, defaulting to 2024 "all activities" when nothing is selected. The measure currently works when nothing is selected, and when a year and an activity are selected, but if you select only a year, it returns a blank.
What do I need to change/add to this formula in order to make it find the "all activities" row for any selected year? My colleague and I have been in so many circles trying to address this and I'm sure there must be a much simpler way! Please if you can help, I'd really appreciate it.
Measure = IF(
HASONEVALUE('Comparison data'[Metric 1]),
VALUES('Comparison data'[Metric 1]),
CALCULATE(
MINA('Comparison data'[Metric 1]),
FILTER(
'Comparison data',
IF(
ISFILTERED('Responses'[Year]),
'Comparison data'[Year] = SELECTEDVALUE('Responses'[Year]),
'Comparison data'[Year] = 2024
) &&
IF(
ISFILTERED('Activity table'[Activity]),
IF(
SELECTEDVALUE('Activity table'[Activity]) = "All",
'Comparison data'[Activity] = "All activities",
'Comparison data'[Activity] = SELECTEDVALUE('Activity table'[Activity])
),
'Comparison data'[Activity] = "All activities"
)
),
'Comparison data'[Year] = IF(ISFILTERED('Responses'[Year]), SELECTEDVALUE('Responses'[Year]), 2024)
)
)
