cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arnault_
Resolver II
Resolver II

MdxScript(Model) / Calulcation error in (...)

Hi All,

I have created several mesures to define a confidence interval for my data.

The measures seem to work fine when I use the "card" visual. When I try to use a "table" visual, I get that error message. Here is the detailed message below. You will also find a link with the pbix.

Any idea how to fix this?

Thanks in advance

 

 

2021-02-11_14h47_17.png

2 ACCEPTED SOLUTIONS

Finally, I have tried the following and it works (it seems).

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IF ( SDev > 0,  Result, BLANK () )

View solution in original post

Hi @Arnault_,

 

Congratulations, my idea is also like: IF ( SDev > 0, Result, BLANK () )

 

Best Regards,

Link

View solution in original post

9 REPLIES 9
Arnault_
Resolver II
Resolver II

Hi community,

I am getting en error with one of the measure I created. My objective was to build a confidence interval.

The measures works when displayed in a "card" visual and does not when displayed in a "table".

Any idea how to fix this?

Thanks 😉

 

Here is the pbix.

Here is the measure's code:

 

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IFERROR ( ROUND ( Result, 3 ), BLANK () )

 

Here is a screenshot with the error message below:

2021-02-11_14h47_17.png

Try returning 

[Selling price (Std Dev)]

 

instead. Most likely that measure is not designed properly and creates a value that is too big for the computation (probably summing up too much)

 

Hi @lbendlin ,

Is it an assumption? Can you be more specific if you have any idea?

Anyway, I have found out that the issue seems to be related to that measure, however, it looks like it does not work when the standard deviation result is blank. I have tried to handle this blank result without success. This is where I am expecting some support from the coommunity. How can I make sure the result of [Selling price - conf. level 95%]  is blank and not an error when [Selling price (Std Dev)] cannot be calculated? 

 

 

v-xulin-mstf
Community Support
Community Support

Hi @Arnault_,

 

The confidence interval can not  correspond to a single line.

And then you need modify the data type of mrasure [Selling price - conf. level 95%].

Here is the output:

11.png

 

Best Regards,

Link

 

Hi @v-xulin-mstf ,

Well, what type should I use? I don't get it.

BTW, I figured out that the measure does not work when the standard deviation cannot be calculated.

Indeed [Selling price - conf. level 95%] depends on [Selling price - Std Dev]. I have tried to handle this error but without succeeding. How can I proceed? The expected output would be: if Std Dev is blank or produces an error, then conf. level = blank.

Hi @Arnault_,

 

If [Selling price - Std Dev] returns the value that does not match the condition, CONFIDENCE.NORM returns the #NUM! error value.

So it is impossible that the measure return blank.

v-xulin-mstf_0-1613381066994.png

Please refer: https://docs.microsoft.com/en-us/dax/confidence-norm-function-dax

 

Best Regards,

Link

Ok, that's the problem. I thaught that we could handle this issue. If one of the argument is not matching, why isn't it possible to get an alternative output which in that case could be "blank" as I suggested.

Something like:

If [Selling price (Std Dev)] ≤ 0, then blank(), otherwise [Selling price - conf. level 95%].

Finally, I have tried the following and it works (it seems).

Selling price - conf. level 95% = 
VAR Alpha = ( 1 - 0.95 )
VAR Size =
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'FACT Order'[packing_line_id] ) ),
        FILTER ( 'FACT Order', 'FACT Order'[selling price] <> 0 )
    )
VAR SDev = [Selling price (Std Dev)]
VAR Result =
    CONFIDENCE.NORM ( Alpha, SDev, Size )
RETURN
    IF ( SDev > 0,  Result, BLANK () )

View solution in original post

Hi @Arnault_,

 

Congratulations, my idea is also like: IF ( SDev > 0, Result, BLANK () )

 

Best Regards,

Link

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors