Forum Discussion

pvarley's avatar
pvarley
Helper I
9 years ago
Solved

Power BI Desktop hangs when adding a column

I'm adding a column to an existing table. The table contains about 20,000 rows, so it should not take long, but after I hit Enter, Power BI Desktop says "Working on it" and never finishes. I have upd...
Need Help
  • pvarley's avatar
    pvarley
    9 years ago
    Anonymous wrote:

    HI pvarley,

     

    I think the performanc issue is related to your masure, each measure will calculate throught the UsersTenants table. If this is a case, your calculate column will calculate about almost 1,368,000,000(19,000* 36,000*2) times.

     

    Regards,

    Xiaoxin Sheng

     

    That may be the problem. I worked around it by moving the string comparison into the query and generating two new columns in the UsersTenants table with True/False values. Now the measures just look at those two values, and the calculation is much faster. The new measures look like this:

     

    CALCULATE( DISTINCTCOUNT( UsersTenants[Email] ),
    FILTER( UsersTenants,
    UsersTenants[IsProvisioningUser] = FALSE && UsersTenants[IsInternalUser] = TRUE
    )
    )

pvarley's avatar
pvarley
Helper I
9 years ago

I created a measure with the same formula as the column, but Power BI will not allow me to use the measure as a filter in a report. That is why I'm using a calculated column.

 

The measures behind the column look like this:

 

Measure = CALCULATE( DISTINCTCOUNT( UsersTenants[Email] ),
FILTER( UsersTenants,
UsersTenants[Email] <> "[email protected]" && FIND("@example.com", UsersTenants[Email], ,0) > 0
)
)

 

The UserTenants table has about 36,000 rows, and the Orgs table, which is related and has the column has about 19.000 rows, so it's not a lot of data. The column takes at least 20 minutes to calculate, which seems like an eternity for 19,000 rows. 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI pvarley,

 

I think the performanc issue is related to your masure, each measure will calculate throught the UsersTenants table. If this is a case, your calculate column will calculate about almost 1,368,000,000(19,000* 36,000*2) times.

 

Regards,

Xiaoxin Sheng

  • pvarley's avatar
    pvarley
    Helper I
    9 years ago
    Anonymous wrote:

    HI pvarley,

     

    I think the performanc issue is related to your masure, each measure will calculate throught the UsersTenants table. If this is a case, your calculate column will calculate about almost 1,368,000,000(19,000* 36,000*2) times.

     

    Regards,

    Xiaoxin Sheng

     

    That may be the problem. I worked around it by moving the string comparison into the query and generating two new columns in the UsersTenants table with True/False values. Now the measures just look at those two values, and the calculation is much faster. The new measures look like this:

     

    CALCULATE( DISTINCTCOUNT( UsersTenants[Email] ),
    FILTER( UsersTenants,
    UsersTenants[IsProvisioningUser] = FALSE && UsersTenants[IsInternalUser] = TRUE
    )
    )