Forum Discussion

krishanw's avatar
krishanw
Icon for Helper II rankHelper II
3 years ago
Solved

how to use multiple condition using measure

I'm using the below measure to get the score, and I want to add another column to this condition 
the column name is  "priorityscore" it retunes the integer value but it not suggesting on measures
 
SWITCH(TRUE(),'Efficiency Matrix'[sum of scorecard]>=20 && 'Efficiency Matrix'[sum of scorecard]<=40,10 )
  • bolfri's avatar
    bolfri
    3 years ago

    What is the PriorityScore meaning?

    Is that:

    1. a constant value in each field
    2. or is that a filtered value from a slicer?

    If it's a constant per represented group then you can consider using AVG('Efficiency Matrix'[PriorityScore])) to get what you want. 

7 Replies

  • Hi you can try:

     

    priorityscore =
    IF (
        'Efficiency Matrix'[sum of scorecard] >= 20
            && 'Efficiency Matrix'[sum of scorecard] <= 40, 10,0
    )
  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage

    Conditions:

    You have a table with columns [priorityscore] and [scorecard]. Both are numbers.

     

    Syntax for column:

    Column = SWITCH(TRUE(),
        [priorityscore] = 0, 0,
        [priorityscore] > 0 && [scorecard]>=20 && [scorecard]<=40,10,
        [scorecard]>=20 && [scorecard]<=40,10
    )

    Syntax measure:

    Measure = SWITCH(TRUE(),
        SUM([priorityscore]) = 0, 0,
        SUM([priorityscore]) > 0 && SUM([scorecard])>=20 && SUM([scorecard])<=40,10,
        SUM([scorecard])>=20 && SUM([scorecard])<=40,10
    )

     Depending on your data you and what values you want to achive you need to consider using SUMX except SUM eg. if you need to SUM only a score with priority higher than 0. My syntax measure do not apply that rule. 

     

    If it's not the case, you can use it. 

    If it's a problem - give us a sample rows and logic that you want to apply.

    • krishanw's avatar
      krishanw
      Icon for Helper II rankHelper II

      Hi 

      I need to write condition as below. Without getting the sum of the [PriorityScore], I want to pass the exact value

       

      Measure 3 = SWITCH(TRUE(),'Efficiency Matrix'[sum of scorecard]>=10 && 'Efficiency Matrix'[sum of scorecard]<=20  && 'Efficiency Matrix'[PriorityScore])=1,10,
      'Efficiency Matrix'[sum of scorecard]>=21 && 'Efficiency Matrix'[sum of scorecard]<=30  && 'Efficiency Matrix'[PriorityScore])=1,20,
      'Efficiency Matrix'[sum of scorecard]>=10 && 'Efficiency Matrix'[sum of scorecard]<=20  && 'Efficiency Matrix'[PriorityScore])=2,30,
      'Efficiency Matrix'[sum of scorecard]>=21 && 'Efficiency Matrix'[sum of scorecard]<=30  && 'Efficiency Matrix'[PriorityScore])=2,40
      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage

        What is the PriorityScore meaning?

        Is that:

        1. a constant value in each field
        2. or is that a filtered value from a slicer?

        If it's a constant per represented group then you can consider using AVG('Efficiency Matrix'[PriorityScore])) to get what you want. 

    • krishanw's avatar
      krishanw
      Icon for Helper II rankHelper II

      Also, I tried using columns 

      Please see the sample 

       

      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]=0 ,12,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=1 && [ScoreCard]>=10 ,11,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=11 && [ScoreCard]>=20,10,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=21 && [ScoreCard]>=30,9,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=31 && [ScoreCard]>=40,8 ,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=41 && [ScoreCard]>=50,7,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=51 && [ScoreCard]>=60,6,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=61 && [ScoreCard]>=70,5,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=71 && [ScoreCard]>=80,4,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=81 && [ScoreCard]>=90,3,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=91 && [ScoreCard]>=100,2,
      IF('Efficiency Matrix'[PriorityScore]=3 && [ScoreCard]<=100 ,1,
      --P4
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]=0 ,11,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=1 && [ScoreCard]>=10 ,10,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=11 && [ScoreCard]>=20,9,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=21 && [ScoreCard]>=30,8,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=31 && [ScoreCard]>=40,7 ,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=41 && [ScoreCard]>=50,6,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=51 && [ScoreCard]>=60,5,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=61 && [ScoreCard]>=70,4,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=71 && [ScoreCard]>=80,3,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=81 && [ScoreCard]>=90,2,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=91 && [ScoreCard]>=100,1,
      IF('Efficiency Matrix'[PriorityScore]=4 && [ScoreCard]<=100 ,0.5)
  • Hi 

    I need to write condition as below. Without getting the sum of the [PriorityScore], I want to pass the exact value

     

    Measure 3 = SWITCH(TRUE(),'Efficiency Matrix'[sum of scorecard]>=10 && 'Efficiency Matrix'[sum of scorecard]<=20  && 'Efficiency Matrix'[PriorityScore])=1,10,
    'Efficiency Matrix'[sum of scorecard]>=21 && 'Efficiency Matrix'[sum of scorecard]<=30  && 'Efficiency Matrix'[PriorityScore])=1,20,
    'Efficiency Matrix'[sum of scorecard]>=10 && 'Efficiency Matrix'[sum of scorecard]<=20  && 'Efficiency Matrix'[PriorityScore])=2,30,
    'Efficiency Matrix'[sum of scorecard]>=21 && 'Efficiency Matrix'[sum of scorecard]<=30  && 'Efficiency Matrix'[PriorityScore])=2,40