Switch condition issue in DAX

[Anonymous]

Hello All,

I have report where I am showing sales and for each date.

My data is like below

TrendType      Date                Sales

Daily	12/8/2022	100
Daily	12/9/2022	100
Daily	12/10/2022	90
Daily	12/11/2022	250
Daily	12/12/2022	210
Daily	12/13/2022	450
Daily	12/14/2022	80
Daily	12/15/2022	70
Daily	12/16/2022	290
Daily	12/17/2022	600
Daily	12/18/2022	700
Daily	12/19/2022	50
Monthly	11/1/2022	450
Monthly	10/1/2022	600
Monthly	9/1/2022	700
Monthly	8/1/2022	300
Monthly	7/1/2022	100
Monthly	6/1/2022	180
Monthly	5/1/2022	650
Monthly	4/1/2022	800
Monthly	3/1/2022	260
Monthly	2/1/2022	170
Monthly	1/1/2022	760
Monthly	12/1/2021	150

I created a sles KPI which will show like below.

KPI_Sales = SWITCH(TRUE(),

sum(Sheet1[Sales]) <=1000, 5,

sum(Sheet1[Sales]) <=600, 4,

sum(Sheet1[Sales])  <=300, 3,

sum(Sheet1[Sales]) <=200, 2,

1)

//if sum of sales <= 200 then 2

//if sum of sales >200 and lessthan equal to 300 then 3

//if sum of sales >300 and lessthan equal to 600 then 4

//if sum of sales >600 and lessthan equal to 1000  then 5

But when I show the kpi for each date I am not getting accurate results.

For all sales the kpi is showing 5.

Am I doing anything wrong here.

Please suggest

[jhavdn]

Hi, the wwitch statement checks every condition as put in so in your case the first one is checks if the outcome is below 1000 (thats true in every case).

try this:

KPI_Sales = SWITCH(TRUE(),

sum(Sheet1[Sales]) <=200, 2,

sum(Sheet1[Sales])  <=300, 3,

sum(Sheet1[Sales]) <=600, 4,

sum(Sheet1[Sales]) <=1000, 5,

1)

if you check it this way you should get better results.

[Mahesh0016 · Accepted solution]

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

[amitchandak]

@Anonymous , You need to create Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k