Issue with ISSELECTED function to detect values selected

[Anonymous]

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

[OwenAuger]

Hi @Anonymous 

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

[Anonymous]

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.

[OwenAuger · Accepted solution]

@Anonymous  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?

[Anonymous]

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