Hello All, I have report where I am showing sales and for each date.My data is like below TrendType Date SalesDaily12/8/2022 100Daily12/9/2022 100Daily12/10/2022 90Daily12/11/2022 250Daily12/12/2022 210Daily12/13/2022 450Daily12/14/2022 80Daily12/15/2022 70Daily12/16/2022 290Daily12/17/2022 600Daily12/18/2022 700Daily12/19/2022 50Monthly11/1/2022 450Monthly10/1/2022 600Monthly9/1/2022 700Monthly8/1/2022300Monthly7/1/2022100Monthly6/1/2022180Monthly5/1/2022650Monthly4/1/2022800Monthly3/1/2022260Monthly2/1/2022170Monthly1/1/2022760Monthly12/1/2021150 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

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

Anonymous

Not applicable

3 years ago

Solved

Switch condition issue in DAX

Hello All,

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

My data is like below

TrendType Date Sales

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,

//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

Mahesh0016
3 years ago
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

amitchandak
Super User
3 years ago
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
Mahesh0016
Super User
3 years ago
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
jhavdn
Regular Visitor
3 years ago
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.