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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Handling infinity value

Hello,

 

I am currently using a measure to calculate Sales Rate. The calculation is as follows FactTable[X]/FactTable[Y]. The X and Y come from different Fact tables. If Y is not present for the corresponding date, I am getting a value of infinity. It would be good to have nothing show up then to have infinity value. How do I achieve this?

 

Thanks in advance

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

Thanks for the reply.

 

I got a solution online before I came across your post. Refer below for the solution.

IFERROR(
    [Mesaure 1]/[Mesaure 2],
    BLANK()
    )

 where Mesaure 1 is Sum(FactTable[x])and Mesaure 2 is Sum(FactTable[y])

 Found it in this blog- https://sqldusty.com/

View solution in original post

7 REPLIES 7
Linnil
Helper II
Helper II

Radacad has a very thorough blog about this - best practice etc.
https://radacad.com/make-your-numeric-division-faultless-in-power-query 

EV
Advocate II
Advocate II

Found this alternative without getting 'Cannot load Visual Error', DIVIDE function

 

DIVIDE(numerator,denominator,0)

 

Reference: https://www.dutchdatadude.com/power-bi-pro-tip-divide-function/ 

This is a better solution than the one that is posted as the solution. Thank you!

hugoberry
Responsive Resident
Responsive Resident

If FactTable[Y] is zero just display blank for example:

Measure = IF(FactTable[Y]=0,BLANK(),FactTable[X]/FactTable[Y])

Anonymous
Not applicable

Hi,

 

Thanks for the reply.

 

I got a solution online before I came across your post. Refer below for the solution.

IFERROR(
    [Mesaure 1]/[Mesaure 2],
    BLANK()
    )

 where Mesaure 1 is Sum(FactTable[x])and Mesaure 2 is Sum(FactTable[y])

 Found it in this blog- https://sqldusty.com/

Thanks, huge help !

IF Error calculation does work, however it takes a load on the memory and fails with 'cannot load visual' error. Is there an alternative to avoid this issue?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.