Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Infinity error

Hello,

 

I have some data about registrations to an online system. I want to create a measure showing the percentage of registrations out of the total population for a region, on a month-by-month (and cumulative) basis. Here is the measure:

 

Adoption Rate3 = 
 iferror(calculate(
 counta('Registrations'[UserId]),
 filter(allselected('Year-Month'[Year-Month]),
 ISONORAFTER('Year-Month'[Year-Month],
 max('Year-Month'[Year-Month]),desc))),
 0)
 / sum(Regions[Population])
However the last line is causing issues (it works exactly as I expect when I remove the final line). It is causing Infinities in my table visualisation so I assume it thinks the population is 0 for these months, but I'm unsure why. The table below is showing a line per region and Year-Month across the top. The error only seems to appear in months where there were no new registrations for the region.
 

 

The tables I have are:

 

Year-Month: one column showing all possible Year-Months (2019-12, 2020-01, 2020-02 etc)

This is where the measure is sat.

 

Registrations: one row per registration - relevant colums are UserID, Region, Year-Month.

One-Many relationship between Year-Month[Year-Month] and Registrations[Year-Month].

 

Regions: one row per region - relevant columns are Region, Population.

One-Many relationship between Regions[Region] and Registrations[Region].

 

Please can you help me identify where the measure is going wrong? Thank you.

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, i create three tables to test:

    Please take following steps:

    1)Create a measure:

    Measure = DIVIDE(COUNTROWS(Registrations),SUM(Regions[Population]))

    Choose a matrix visual and it shows:

    2)Create another measure:

    Measure 2 =
VAR d =
    SELECTEDVALUE ( 'Year-Month'[Year-Month] )
RETURN
    IF (
        COUNTROWS ( 'Registrations' ) = 0,
        VAR t =
            FILTER (
                SUMMARIZE (
                    FILTER ( ALLSELECTED ( 'Year-Month' ), 'Year-Month'[Year-Month] < d ),
                    'Year-Month'[Year-Month].[Year],
                    'Year-Month'[Year-Month].[MonthNo],
                    "Number", CALCULATE ( COUNTROWS ( 'Registrations' ) )
                ),
                [Number] > 0
            )
        RETURN
            CALCULATE (
                [Measure],
                FILTER (
                    ALLSELECTED ( 'Registrations' ),
                    'Registrations'[Region] IN FILTERS ( 'Registrations'[Region] )
                        && MONTH ( 'Registrations'[Year-Month] ) = MAXX ( t, [Year-Month].[MonthNo] )
                        && YEAR ( 'Registrations'[Year-Month] ) = MAXX ( t, [Year-Month].[Year] )
                )
            ),
        [Measure]
    )

    And it replace the original blank with previous month's data, the result shows:

    Here is my test pbix file:

    pbix 

     

    Best Regards,

    Giotto ZHi

8 Replies