Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
This is i want
Thanks,
Sakthi
@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
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
Hi Xiaoxin Sheng,
Thanks for replying.
I tried the query, but it didn't work even internally
Kindly let me know if any alternative.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |