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!
gmarinov's avatar
gmarinov
Frequent Visitor
4 years ago

Hi KNP,

 

The reason I did it in Power Query is that i wanted to clean the subscriptions table before it is loaded for the report users.

I did the total payments calculation as DAX column instead and it worked a treat! The only thing is that in order to clean the data, report users need to add a page filter to exclude Subscriptions where TotalPayments are 0 or (blank). Do you know if there is a way in DAX to filter the table with a measure so they don't have to do page filters/slicers?

 

Kind regards,

George

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!