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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,089,978
Messages
5,411,627
Members
403,383
Latest member
Excelacity

This Week's Hot Topics

Top