Filtering a table before Merge?

[lancersc]

Hello!

So below are the tables and its contents. What im planning to do is filter the invoice month and branch code of Table A and Table B before inner joining them.

Another good thing is having the Invoice Month and Branch Code on a Visual slicer so I could change them whenever.

This is easy to do in excel but the 900k+ data is just too big and everything just freezes.

Is this possible to do? thanks!!

Table A

Invoice Month

Branch Code

Item ID

Total Item Price

Table B

Invoice Month

Branch Code

Item ID

Total Item Price

[lancersc · Accepted solution]

Figured it out. I just used a dynamic Filter. 

[MahyarTF]

Not sure, if you are talking about Power BI ?

If yes, in Power Query you could delete the columns before merging the tables, also in Power Bi, you could use the particular columns in the CrossJoin function in calculated table 

Appreciate your Kudos and please mark it as a solution if it helps you.

[lancersc]

Yes. Boss wants the whole process to be on PowerBI and im having a hard time making it work. Want to take a look at the query? 

Why would I delete a column?

Does crossjoin inner join the 2 datas?

[amitchandak]

@lancersc , if you plan to merge tables with rows filtered using a slicer (Visual), I doubt that is possible.

You can use merge is power query, select multiple columns with shift and join or Use crossjoin or Generate in DAX as suggested by @MahyarTF 

[lancersc]

hmm.. cant seem to understand it properly. This is the query I use to get the data I need. Just incase you guys have any idea how I can navigate around it.

I just want a slicer for BranchCode and Range 1 & 2

If I cant do this, can I just do Excel to PowerBI? Everytime I make changes in Excel it will reflect in BI?

DECLARE @BranchCode VARCHAR(500) = '01001,01023,01025,01027,01031'
DECLARE @Range1 VARCHAR(255) = '202101,202102,202103,202104,202105,202106,202107,202108,202109,202110,202111'
DECLARE @Range2 VARCHAR(255) = '202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211'

SELECT
ROW_NUMBER() OVER(ORDER BY sub1.ValueGap ASC,ItemID) AS RN_VG,
ROW_NUMBER() OVER(ORDER BY sub1.R2_ItemPrice DESC,ItemID) AS RN_R2IP,
sub1.*

FROM

( Lots of formula

FROM

(
SELECT
ItemID,
SUM(TotalItemPrice) AS TotalItemPrice,
SUM(TotalItemCost) AS TotalItemCost,
SUM(TotalItemQty) AS TotalItemQty
FROM
DM.MonthlyItemCogs
WHERE
0 = 0
AND InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range1,','))
AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchCode,N','))
GROUP BY
ItemID
) r1

INNER JOIN

(
SELECT
ItemID,
SUM(TotalItemPrice) AS TotalItemPrice,
SUM(TotalItemCost) AS TotalItemCost,
SUM(TotalItemQty) AS TotalItemQty
FROM
DM.MonthlyItemCogs
WHERE
0 = 0
AND InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range2,','))
AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchCode,N','))
GROUP BY
ItemID
) r2

Formulas

) sub1