cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Issue with ISSELECTED function to detect values selected

Hi,

I have a time dimesnion and slicers built on top of tat, the user has the option to select multiple values in a slicer

Week Slicer  Month Slicer  Quarter Slicer  Year Slicer

 

My requirement is if user selects a week then the measure should use one column say Col A from the fact table and anything above the week, I need to aggregate using Col B from fact table. For this I am using the below formula

 

Week Check= SWITCH(
                                      TRUE(),
                                      Not ISBLANK(SELECTEDVALUE(DIM_WeekK[WK])),1,
                                      Not ISBLANK(SELECTEDVALUE(DIM_Month[Month])),2,
                                      Not ISBLANK(SELECTEDVALUE(DIM_Month[Quarter])),3,
                                      Not ISBLANK(SELECTEDVALUE(DIM_Month[Year])),4

                                   )

 

and in my measure

 
Sales = SWITCH(
                          TRUE(),
                          [Week Check]=1, SUM(Fact[Col A]),
                          [Week Check]>1, sum(Fact[Col B] )
                        )

 

The logic works fine with only one value selected in each slicer, but it breaks when user selects multiple values..Say when user selects 2 weeks in week slicer, I want my query to aggregate from Col A, but due to ISSELECETD function its going to next level and aggreagting from Col B.

 

Please suggest if there is any other alternative

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Issue with ISSELECTED function to detect values selected

@pradeep467  sorry to hear that's not working.

 

The logic works in a test model I built, so I guess there is something specific to your model that is stopping the [Week Check] measure working.

 

Could you post a link to a santised PBIX that exhibits the problem?


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Issue with ISSELECTED function to detect values selected

Hi @pradeep467 

 

You could use ISFILTERED instead, to detect whether any filters are applied to the columns of interest:

 

Week Check =
SWITCH (
    TRUE (),
    ISFILTERED ( DIM_WeekK[WK] ), 1,
    ISFILTERED ( DIM_Month[Month] ), 2,
    ISFILTERED ( DIM_Month[Quarter] ), 3,
    ISFILTERED ( DIM_Month[Year] ), 4
)

 

 

Also, you can change the Sales measure slightly (as the condition depends only on the value of [Week Check] and change it so that it evaluates [Week Check] only once:

Sales =
SWITCH (
    [Week Check],
    1, SUM ( Fact[Col A] ),
    2, SUM ( Fact[Col B] )
)

 

Does that give the intended result?

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Helper II
Helper II

Re: Issue with ISSELECTED function to detect values selected

Thanks for the quick response Owen, I tried doing that change but its still not giving the result I am expecting

When I select 2 weeks from week slicer, I expect the Week check to still stay at 1 , but it shows value where only a single value is selected, in my case if I selecetd 2 values in week, 2 in month and  1 value in Qtr then its showing 3 but I am expecting it to show 1.

Highlighted
Super User I
Super User I

Re: Issue with ISSELECTED function to detect values selected

@pradeep467  sorry to hear that's not working.

 

The logic works in a test model I built, so I guess there is something specific to your model that is stopping the [Week Check] measure working.

 

Could you post a link to a santised PBIX that exhibits the problem?


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Helper II
Helper II

Re: Issue with ISSELECTED function to detect values selected

Thanks Owen, I was using a wrong column, its working now.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors