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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors