How to DAX for a complex sql query like filter date first, then group by, then join other table

[River]

Hi Friends,

Need to convert a complex sql query to PowerBI DAX.

First, need a date filter, to filter out table A, then aggregate 'Payment' column in table A grouping by 'Invoice', 'Account' column, thirdly, join table B on 'Invoice' and 'Account' column.

Date is from date slicer.  Difficulty is that date is dynamic, filtering first then group by, how to do it?

BTW, grouping on 'date' is not going to work in this case.

Any suggestion is greatly appreciated.

[River]

I have tried, it worked but I am verifying data, it seems the date filter is not in efffect, which is on table A. 

[smpa01]

@River -Date is from date slicer - I thought you are using a calendar Table for that. Is it kindly possible for you to produce a small sample pbix and share here?

[River]

@smpa01 confirmed, it's not filtering the date on table A, instead it's getting all data for all dates.  

yes, I am using calendar table, it's linked to the date field on table A, date slicer is linked to calendar table. Unfortunately, I can't upload a pbix with real data.  

[smpa01]

@River is it posssible for you to create a sample pbix with fake data with only relvant columns and nothing else in a very small scale?

[River]

@smpa01 didn't find an option to upload pbix file

[River]

@smpa01 https://1drv.ms/u/s!Aq08cehlu9wukAipaxYcpMde6UzP?e=hfr9eD

Here is link to the pbix. Let me know if you can access it.

[smpa01 · Accepted solution]

@River  thanks for this.

I had a thorough look into it.

The measure I gave you generates this

But If we disect things, for the given date range, 

it returns following

On the other hand, if we disect Invoice table with exact same values it returns following

So you would have like a measure, which generates only SUM of 

'Invoice Paid'[SETTLEAMOUNTMST]

for only 'Invoice'[AccountNum]=3

Please change your measure to this

Amount Paid = 
CALCULATE (
    - SUM ( 'Invoice Paid'[SETTLEAMOUNTMST] ), --this gets sliced as per Date tbl, there is no need to explicitly
    // ALLEXCEPT (                             -- put a Date Filter
    //     'Invoice Paid',
    //     'Invoice Paid'[AccountNum],
    //     'Invoice Paid'[LastSettleVoucher],    --ALLEXCEPT is not required here cause 
    //     'Invoice Paid'[DataAreaId],           -- 'Invoice Paid'[AccountNum],'Invoice Paid'[LastSettleVoucher]
    //     'Invoice Paid'[Partition]             -- 'Invoice Paid'[DataAreaId],'Invoice Paid'[Partition]
    // ),                                        -- is explictly mentioned in TREATAS which generates the sum
    TREATAS (                                    -- as per that grouping
        SUMMARIZE (
            'Invoice',
            'Invoice'[AccountNum],
            'Invoice'[Voucher],
            'Invoice'[DataAreaId],
            'Invoice'[Partition]
        ),
        'Invoice Paid'[AccountNum],              
        'Invoice Paid'[LastSettleVoucher],
        'Invoice Paid'[DataAreaId],
        'Invoice Paid'[Partition]
    )
)

[River]

@smpa01  That did the trick, thank you so much!

[River]

@smpa01 I am doing it. Will upload soon.

[parry2k]

@River maybe it is easier if you just set a relationship between Table A and Table B, by creating a surrogate key that is the concatenation of account + invoice, once this relationship is established, then you just need a simple sum measure to visualize and slice/dice the data.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[smpa01]

@River  give this a try?

Measure =
CALCULATE ( 
    SUM ( tblA[Payment] ),
    ALLEXCEPT ( tblA, tblA[Invoice], tblA[Account] ),
    TREATAS (
        SUMMARIZE ( tblB, tblB[Invoice], tblB[Account] ),
        tblA[Invoice],
        tblA[Account]
    )
)

[smpa01]

@River  did you have a chance to test out?

[bcdobbs]

Am a big fan of TREATAS for moving filters around. Never seen your use of summarize inside it, love it will be borrowing! Thanks.

[River]

Hi Smpa01

Thanks a lot for quick response, my questios are, where is date filter coming to play? and we are talking about many records of 'invoice'  and 'account' combination.

[smpa01]

@River  you said you have a slicer for Date..so we don't need to account Date seperately in the DAX expression. The slicer is there and the expression takes that into account.

[River]

Ok, I see, will give a try.