filtering with a table

martingaleh

Board Regular
Joined
Jul 18, 2011
Messages
79
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.

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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
82
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:

martingaleh

Board Regular
Joined
Jul 18, 2011
Messages
79
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,718
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top