filtering with a table

martingaleh

Board Regular
Joined
Jul 18, 2011
Messages
83
Hi I have this table Query1 below. I want to return the distinct count of companies where the company is in the list of companies where the filter context of campaign = a and the date is greater than the minimum date of the filter context, which in this case would be 4/1/2019 because the filter context has campaign = a and the campaign is blank.
O0JCA8.jpg

If I was to do this in sql, it would be
select count(*) from Query1 where company in (select company from Query1 where campaign='a') and date > (select min(date) from Query1 where campaign = 'a') and campaign = ''

So in this case, the answer would be 2. Only a,b,c were in campaign a. D was in campaign b only so it doesn't count. Only a, b were also in a campaign value that is blank with a date greater than 4/1/2019.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Not sure if you are looking for Dax or M , but does this work for you? Change row1 to reflect your actual source
Filters in next to last row

Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"company"}, {{"MaxDate", each List.Max([date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"company"},#"Grouped Rows",{"company"},"Table",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"MaxDate"}, {"MaxDate"}),
    #"CountUniques" = Table.RowCount(Table.SelectRows(#"Expanded Table1", each ([campaign] = "a") and ([MaxDate] > #date(2019, 4, 1))))
in #"CountUniques"
 
Last edited:
Upvote 0
Not sure if you are looking for Dax or M , but does this work for you? Change row1 to reflect your actual source
Filters in next to last row

Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"company"}, {{"MaxDate", each List.Max([date]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"company"},#"Grouped Rows",{"company"},"Table",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"MaxDate"}, {"MaxDate"}),
    #"CountUniques" = Table.RowCount(Table.SelectRows(#"Expanded Table1", each ([campaign] = "a") and ([MaxDate] > #date(2019, 4, 1))))
in #"CountUniques"

Thanks. I was looking for a DAX solution which would have campaign in the filter context from the pivot table rows. M is way easier than dax...
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top