Reply
Regular Visitor
Posts: 19
Registered: ‎09-06-2018
Accepted Solution

If or Switch to filter

Hi,

 

I'm looking for a formula to do some counting in a column of numbers, but I'm struggling.

 

I need it to say this.

If data in column "Variance" = <6 leave then as it is, else divide the number by 7 and multiply by 5.

So, if the data in column "Variance" is 3, for example, then it will stay as 3. If it is 48, then it will display 34 (rounded up or down).

 

I've browsed a lot of similar formulae on this site, but nothing so specific as it will help. I've also seen lots of suggestions for the Switch function, but not sure if it applies here.

 

Many thanks,

Rich


Accepted Solutions
Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: If or Switch to filter

[ Edited ]

Hi @RichJW,

 

You can use either IF either SWITCH:

 

Custom = 
         IF( 
             [Variance] <= 6,
             [Variance],
             ROUNDDOWN([Variance] * 5 / 7, 0)
         )
Custom2 = 
         SWITCH(
             TRUE(), 
             [Variance]<= 6, [Variance],
             ROUNDDOWN([Variance] * 5 / 7,0)
         )

if or switch 2.PNG

Reference to

IF explanation - https://docs.microsoft.com/en-us/dax/if-function-dax 

and SWITCH - https://docs.microsoft.com/en-us/dax/switch-function-dax

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

View solution in original post


All Replies
SPG Member
Member
Posts: 49
Registered: ‎07-25-2018

Re: If or Switch to filter

[ Edited ]

Just create a new column wiht the condition:

 

Column = IF(Table[Variance] <= 6, Table[Variance], ROUND(Table[Variance] / 7 * 5))

 

Highlighted
Advisor
Posts: 307
Registered: ‎01-16-2018

Re: If or Switch to filter

[ Edited ]

Hi @RichJW,

 

You can use either IF either SWITCH:

 

Custom = 
         IF( 
             [Variance] <= 6,
             [Variance],
             ROUNDDOWN([Variance] * 5 / 7, 0)
         )
Custom2 = 
         SWITCH(
             TRUE(), 
             [Variance]<= 6, [Variance],
             ROUNDDOWN([Variance] * 5 / 7,0)
         )

if or switch 2.PNG

Reference to

IF explanation - https://docs.microsoft.com/en-us/dax/if-function-dax 

and SWITCH - https://docs.microsoft.com/en-us/dax/switch-function-dax

 

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!

Frequent Visitor
Posts: 5
Registered: ‎11-09-2018

Re: If or Switch to filter

That should work for you 

 

= IF(SUM(Table[Variance]) > 6, ROUND(DIVIDE(SUM(Table[Variance]),7) * 5,0), SUM(Table[Variance]))

Regular Visitor
Posts: 19
Registered: ‎09-06-2018

Re: If or Switch to filter

Hi @SPG, @zoloturu and @aaronwatt.

 

Many thanks for your responses.

 

I tried SPG's first and received an error regarding the minimum argument count in the ROUND function, however I quickly moved onto zoloturu's and that one worked perfectly for me.

I'll try aaronwatt's as well, as it's always nice to have options Smiley Happy

 

Many thanks all,

Cheers,

Rich