Forum Discussion

gmarinov's avatar
gmarinov
Frequent Visitor
4 years ago
Solved

Power Query too slow to load when adding a calculated column from another table

Hi There,   I have two tables:   PaymentsTable which has a list of all payments and their amounts plus SubscriptionID which dentifies which subscription a payment relates to. SubscriptionsTable ...
Need Help
  • KNP's avatar
    KNP
    4 years ago

    Hi George,

     

    Would be good if you could share a screenshot of your data model with relationships.

    Also, some sample data if you can.

     

    Avoid calculated columns if you can. They're often not necessary and just end up bloating your model.

     

    Create a new measure...

    Payment Amount = SUM(PaymentsTable[Amount])

    Drop that in your visual and then we can figure out what filters/other measures you need.

     

    Regards,

    Kim

  • gmarinov's avatar
    gmarinov
    4 years ago

    Thanks Kim,

     

    I did what you said with an added filters for the SubscriptionID:

     

    PaymentsTotal =
    CALCULATE(
        SUM('PaymentsTable'[Amount]),
        FILTER(
            ALL(PaymentsTable')
            ,'PaymentsTable'[SubscriptionID] IN VALUES ('SubscriptionsTable'[SubscriptionID])
        )
    )
     
    I am then using the DonationsTotal measure as a nested filter in my other measures:
     
    CALCULATE(
        [OTHER MEASURES]
        ,FILTER(
            'SubscriptionsTable'
            ,AND(
                'SubscriptionsTable'[PaymentsTotal]>0
                ,ISBLANK('SubscriptionsTable'[PaymentsTotal])=FALSE()
            )
        )
     
    That seems to be working and i will accept your suggestion as a solution.
     
    Many thanks for your help!
KNP's avatar
KNP
Super User
4 years ago

Hi George,

 

Would be good if you could share a screenshot of your data model with relationships.

Also, some sample data if you can.

 

Avoid calculated columns if you can. They're often not necessary and just end up bloating your model.

 

Create a new measure...

Payment Amount = SUM(PaymentsTable[Amount])

Drop that in your visual and then we can figure out what filters/other measures you need.

 

Regards,

Kim

gmarinov's avatar
gmarinov
Frequent Visitor
4 years ago

Thanks Kim,

 

I did what you said with an added filters for the SubscriptionID:

 

PaymentsTotal =
CALCULATE(
    SUM('PaymentsTable'[Amount]),
    FILTER(
        ALL(PaymentsTable')
        ,'PaymentsTable'[SubscriptionID] IN VALUES ('SubscriptionsTable'[SubscriptionID])
    )
)
 
I am then using the DonationsTotal measure as a nested filter in my other measures:
 
CALCULATE(
    [OTHER MEASURES]
    ,FILTER(
        'SubscriptionsTable'
        ,AND(
            'SubscriptionsTable'[PaymentsTotal]>0
            ,ISBLANK('SubscriptionsTable'[PaymentsTotal])=FALSE()
        )
    )
 
That seems to be working and i will accept your suggestion as a solution.
 
Many thanks for your help!