filtering with a table

[martingaleh]

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.

 

[horseyride]

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

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"

 

[martingaleh]

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

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...