Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Solved! Go to 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?
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
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?
Thanks Owen, I was using a wrong column, its working now.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |