how to optimize my dax

[hylosko]

hello i have 3 measure

1. it is max month = MAX('Calendar'[Date].[Month number])

this measure give me the max number of month in year

2. Last value = calculate([max month],filter( 'Table1',Table1[Value] <> blank()))

this measure give me the number of last month with value ( for example if last value was for may it show me 5, if last value was for march it show me 3 etc)

3. Measure = if([Last value] = 7, (calculate(Table1[Value], filter('Calendar','Calendar'[Date].[Month number] = 6))), if([Last value] = 6, (calculate(Table1[Value], filter('Calendar','Calendar'[Date].[Month number] = 5))), if([Last value] = 5, (calculate(Table1[Value], filter('Calendar','Calendar'[Date].[Month number] = 4))), if([Last value] = 4, (calculate(Table1[Value], filter('Calendar','Calendar'[Date].[Month number] = 3))), if([Last value] = 3, (calculate(Table1[Value], filter('Calendar','Calendar'[Date].[Month number] = 2))),"xd")))))

that measure give us value for month before last month with value (for example if in may we have value 50 and in april 30, it will show 30 etc)

it any possibilty to optimize this dax ?

[Anonymous · Accepted solution]

Hi @hylosko ,

Here is an example.

-------- IF form ------------
SizeDesc =
IF ( Product[Size] = "S", "Small",
IF ( Product[Size] = "M", "Medium",
IF ( Product[Size] = "L", "Large",
IF ( Product[Size] = "XL", "Extra Large", "Other" ) ) ) )

-------- SWITCH equivalent ------------
SizeDesc =
SWITCH (
Product[Size],
"S", "Small",
"M", "Medium",
"L", "Large",
"XL", "Extra Large",
"Other"
)

Please try this measure.

Measure = 
SWITCH(
    [Last value],
    7,CALCULATE(MAX('Table1'[Value]),MONTH('Calendar'[Date]) = 6),
    6,CALCULATE(MAX('Table1'[Value]),MONTH('Calendar'[Date]) = 5),
    5,CALCULATE(MAX('Table1'[Value]),MONTH('Calendar'[Date]) = 4),
    4,CALCULATE(MAX('Table1'[Value]),MONTH('Calendar'[Date])= 3),
    3,CALCULATE(MAX('Table1'[Value]),MONTH('Calendar'[Date]) = 2),
    "xd"
)

If this doesn't work for you, consider providing a sample file with no private data.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[NickolajJessen]

First of, instead of using multiple IF-statements, the better opstion is to use SWITCH.

Secondly, for a query like this, you should probably be looking towards some of power bi's built in time intelligence functions

Edit:

[hylosko]

Can you tell me more about that switch functions ? 

[NickolajJessen]

https://www.youtube.com/watch?v=UsAJPIANAb8

[NickolajJessen]