Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

replace cell value with conditional value and multiply to output value

Hello all,
I am new to power bi and need help with a calculation.

I have three columns: "Anzahl Lieferanten", "Anzahl Hersteller" and "Zeit bis EOL" (in days). Now I want to assign a value from 1-3 to each of the values in the columns if they are greater or less than certain values. the resulting values I then want to multiply.
I have thought of something like this:

 

 

 

Of course, it doesn't work that way. Maybe someone can help me? Thanks in advance!

 

  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hello Anonymous ,

    The issue is with the syntax of the switch statement.

    Obsoleszenzwahrscheinlichkeit =

    var Lieferantenrisiko =
    SWITCH(
    TRUE(),
    Inventory[Anzahl Lieferanten] = 1, 3,
    Inventory[Anzahl Lieferanten] > 1 && Inventory[Anzahl Lieferanten] < 4, 2,
    Inventory[Anzahl Lieferanten] > 3, 1
    )


    var Herstellerrisiko =
    SWITCH(
    TRUE(),
    Inventory[Anzahl Hersteller] = 1, 3,
    Inventory[Anzahl Hersteller] > 1 && Inventory[Anzahl Hersteller] < 4, 2,
    Inventory[Anzahl Hersteller] > 3, 1
    )

    var EOLRisiko =
    SWITCH(
    TRUE(),
    Inventory[Zeit bis EOL] < 365, 1,
    Inventory[Zeit bis EOL] >= 365 && Inventory[Zeit bis EOL] <= 730, 2,
    Inventory[Zeit bis EOL] > 730, 3
    )

    var result =
    (2 * Lieferantenrisiko) * Herstellerrisiko * EOLRisiko

    Return
    result
     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    You missed the (  ,  )  after TRUE ( ) in the first variable. Also delete the (  " "  ) from around the numbers. Eg. write 1 not "1"

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hello Anonymous ,

    The issue is with the syntax of the switch statement.

    Obsoleszenzwahrscheinlichkeit =

    var Lieferantenrisiko =
    SWITCH(
    TRUE(),
    Inventory[Anzahl Lieferanten] = 1, 3,
    Inventory[Anzahl Lieferanten] > 1 && Inventory[Anzahl Lieferanten] < 4, 2,
    Inventory[Anzahl Lieferanten] > 3, 1
    )


    var Herstellerrisiko =
    SWITCH(
    TRUE(),
    Inventory[Anzahl Hersteller] = 1, 3,
    Inventory[Anzahl Hersteller] > 1 && Inventory[Anzahl Hersteller] < 4, 2,
    Inventory[Anzahl Hersteller] > 3, 1
    )

    var EOLRisiko =
    SWITCH(
    TRUE(),
    Inventory[Zeit bis EOL] < 365, 1,
    Inventory[Zeit bis EOL] >= 365 && Inventory[Zeit bis EOL] <= 730, 2,
    Inventory[Zeit bis EOL] > 730, 3
    )

    var result =
    (2 * Lieferantenrisiko) * Herstellerrisiko * EOLRisiko

    Return
    result
     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much Rohit!

      Now it works perfectly 🙂