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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
uif19085
Helper III
Helper III

A faster way to calculate a column

Regarding this post: https://community.powerbi.com/t5/Desktop/DAX-Column-if-all-links-are-implemented/m-p/3154958#M106580...

The solution i got from this topic seem to be a slow, i had to leave my laptop over night just for the column to be calculated. This would be a problem when i'll refresh my data. Is there a way to make this calculated column faster?

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @uif19085 ,

 

You can use DAX Studio to capture DAX queries and then use the Power BI Profiler.
You can refer to the following document to optimize calculated columns.

Calculated Table Columns Power BI: Tips & Best Practices (enterprisedna.co)


I recommend that you use measures instead of calculated columns, and measures perform calculations only when they are used in the report. If you don't use them, they don't take up any space in the model.

vtangjiemsft_0-1680229896095.png

Measure = 
VAR _linkids =
    CALCULATETABLE (
        VALUES ( 'Table 2'[LinkedID] ),
        FILTER ( 'Table 2', MAX('Table 2'[ID]) = MAX('Table 1'[ID]) )
    )
VAR _count =
    COUNTROWS ( _linkids)
VAR _impcount =
    CALCULATE (
        COUNT ( 'Table 1'[Implementation] ),
        FILTER (
            ALL('Table 1'),
            'Table 1'[UniqueID]
                IN _linkids
                && 'Table 1'[Implementation] = "Yes"
        )
    )
RETURN
    IF (
        _count = _impcount
            && NOT ( ISBLANK ( _count ) ) && NOT ( ISBLANK ( _impcount ) ),
        1,
        0
    )

vtangjiemsft_1-1680229935069.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

 

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

Hi @uif19085 ,

 

You can use DAX Studio to capture DAX queries and then use the Power BI Profiler.
You can refer to the following document to optimize calculated columns.

Calculated Table Columns Power BI: Tips & Best Practices (enterprisedna.co)


I recommend that you use measures instead of calculated columns, and measures perform calculations only when they are used in the report. If you don't use them, they don't take up any space in the model.

vtangjiemsft_0-1680229896095.png

Measure = 
VAR _linkids =
    CALCULATETABLE (
        VALUES ( 'Table 2'[LinkedID] ),
        FILTER ( 'Table 2', MAX('Table 2'[ID]) = MAX('Table 1'[ID]) )
    )
VAR _count =
    COUNTROWS ( _linkids)
VAR _impcount =
    CALCULATE (
        COUNT ( 'Table 1'[Implementation] ),
        FILTER (
            ALL('Table 1'),
            'Table 1'[UniqueID]
                IN _linkids
                && 'Table 1'[Implementation] = "Yes"
        )
    )
RETURN
    IF (
        _count = _impcount
            && NOT ( ISBLANK ( _count ) ) && NOT ( ISBLANK ( _impcount ) ),
        1,
        0
    )

vtangjiemsft_1-1680229935069.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!