Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Continued Contributor
Continued Contributor

@Anonymous 
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 @Anonymous,

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.
Anonymous
Not applicable

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 @Anonymous,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.