cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pradeep467
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

@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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User I
Super User I

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!

My Blog
Connect on Twitter
Connect on LinkedIn

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.

@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!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors