Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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!
Blog
Twitter
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!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.