cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sakthivel
Regular Visitor

How to optimize the measure using many if statements

Hi everyone,

       I have develop custom visual and using the measures with many if statement. it's works well while select one month, i will get an error if i choose multiple month.

   I have mentioned the measure. kindly Let me know if any one have answer.

 

Measure:

Rev from Ops = CALCULATE(SUMX('Revenue from Operations',
//Advertisement
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1]= "HTT Daily",[AD HTT Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1] = "VK Daily",[AD VK Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1] = "TT Publication",[AD TT Publication],
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1]= "ht.in portal",([Ad ht.in portal]),

//Event/Activations/Promos
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "HTT Daily",[Event HTT Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "VK Daily",[Event VK Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "TT Publication",[Event TT Publication],
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "Digital Others",[Webinar Revenue with GST],

//Circulation/Subscription
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "HTT Daily",[Cir HTT Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "VK Daily",[Cir VK Daily],
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "TT Publication",[Cir TT Publication],
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "HTT e-paper",[E-Paper],
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "KD e-Magazine",[E-Magazine],

//Others
IF('Revenue from Operations'[Revenue from Operations] = "Others" && 'Revenue from Operations'[Column1] = "TT Publication",[Book_Rev],
IF('Revenue from Operations'[Revenue from Operations] = "Others" && 'Revenue from Operations'[Column1] = "HTT Daily",([HTT Others]),

//Print Total
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1]= "Print Total",([AD Print Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "Print Total",([Event Print Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "Print Total",([Cir Print Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Others" && 'Revenue from Operations'[Column1] = "Print Total",([Others Print Total]),

//Digital Total
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1]= "Digital Total",([Ad ht.in portal]),
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "Digital Total",[Webinar Revenue with GST],
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "Digital Total",([E-Paper]+[E-Magazine]),

//Total
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "HTT Daily",([Total HTT Daily]),
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1] = "TT Publication",([Total TT Publication]),
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1] = "Print Total",
([Total Print Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "ht.in portal",([Ad ht.in portal]),
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "HTT e-paper",[E-Paper],
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "KD e-Magazine",[E-Magazine],
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "Digital Total",
([Total Digital Total]),

//Grand Total
IF('Revenue from Operations'[Revenue from Operations] = "Advertisement" && 'Revenue from Operations'[Column1]= "Total",([Ad Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Event/Activations/Promos" && 'Revenue from Operations'[Column1] = "Total",([Event Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Circulation/Subscription" && 'Revenue from Operations'[Column1] = "Total",([Cir Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Others" && 'Revenue from Operations'[Column1] = "Total",([Others Total]),
IF('Revenue from Operations'[Revenue from Operations] = "Total" && 'Revenue from Operations'[Column1]= "Total",([Total Total])



))))))))))))))))))))))))))))))))))))

 

sakthivel_2-1626424913495.pngsakthivel_3-1626424957778.png

This is i want 

sakthivel_4-1626425361565.png

 

 

 

Thanks,

Sakthi

 

 

4 REPLIES 4
m3tr01d
Resolver III
Resolver III

@sakthivel 
One of the first thing that you shouldn't do is using IF Statement inside an iterator over a Fact Table. 
Now, you are using 30 IF statements in SUMX( iterator). 

The problem is either your Data model or the way you do your underlying measures (Measure inside the IF statements).

We can help you but we will need to have more information on what are your tables and what is the final goal/visual that you want to have


 

v-shex-msft
Community Support
Community Support

HI @sakthivel,

You can try to use the following formula if helps: (I summarize and simple the formula conditions)

 

Rev from Ops =
CALCULATE (
    SUMX (
        'Revenue from Operations',
        //Advertisement
        SWITCH (
            'Revenue from Operations'[Revenue from Operations],
            "Advertisement",
                SWITCH (
                    'Revenue from Operations'[Column1],
                    "HTT Daily", [AD HTT Daily],
                    "VK Daily", [AD VK Daily],
                    "TT Publication", [AD TT Publication],
                    "ht.in portal", [Ad ht.in portal],
                    "Print Total", [AD Print Total],
                    "Digital Total", [Ad ht.in portal],
                    "Total", [Ad Total]
                ),
            //Event/Activations/Promos
            "Event/Activations/Promos",
                SWITCH (
                    'Revenue from Operations'[Column1],
                    "HTT Daily", [Event HTT Daily],
                    "VK Daily", [Event VK Daily],
                    "TT Publication", [Event TT Publication],
                    "Digital Others", [Webinar Revenue with GST],
                    "Print Total", [Event Print Total],
                    "Digital Total", [Webinar Revenue with GST],
                    "Total", [Event Total]
                ),
            //Circulation/Subscription
            "Circulation/Subscription",
                SWITCH (
                    'Revenue from Operations'[Column1],
                    "HTT Daily", [Cir HTT Daily],
                    "VK Daily", [Cir VK Daily],
                    "TT Publication", [Cir TT Publication],
                    "HTT e-paper", [E-Paper],
                    "KD e-Magazine", [E-Magazine],
                    "Print Total", [Cir Print Total],
                    "Digital Total", [E-Paper] + [E-Magazine],
                    "Total", [Cir Total]
                ),
            //Total
            "Total",
                SWITCH (
                    'Revenue from Operations'[Column1],
                    "HTT Daily", [Total HTT Daily],
                    "TT Publication", [Total TT Publication],
                    "Print Total", [Total Print Total],
                    "ht.in portal", [Ad ht.in portal],
                    "HTT e-paper", [E-Paper],
                    "KD e-Magazine", [E-Magazine],
                    "Digital Total", [Total Digital Total],
                    "Total", [Total Total]
                ),            
            //Others
            "Others",
                SWITCH (
                    'Revenue from Operations'[Column1],
                    "TT Publication", [Book_Rev],
                    "HTT Daily", [HTT Others],
                    "Print Total", [Others Print Total],
                    "Total", [Others Total]
                ),
        )
    )
)

 

In addition, power bi service does not suitable for complex formula calculations. (since they are host on the cloud server, so they obviously include some limitations of DAX formula calculate and resource usages)

If you are handling a huge amount of conditions, I'd like to suggest creating a mapping table for these conditions mappings and then you can write a simple formula to lookup these conditions.

BTW, you can also take a look at the below blogs about optimizing the performance of nest measures and iterators.

Optimizing DAX expressions involving multiple measures - SQLBI

Optimizing nested iterators in DAX - SQLBI
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin Sheng,
     Thanks for replying.

      I tried the query, but it didn't work even internally

      Kindly let me know if any alternative.

sakthivel_0-1626869324805.png

 

 

Thanks & regards,

Sakthi

 

Hi @sakthivel,

This notice measures these nested expressions are too complex to calculate.
Did detail level measures include iteration or looping? Can you please share some more about these child-level expressions?

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.