Filtering before group by

[sshikari]

I have data that needs to:

1. be filtered by date when pulled from the data source 

2. grouped by 4 of the fields 

In my visualization, I want the user to be able to pick the date that filteres in step #1.  I think a similar question has been asked before: https://stackoverflow.com/questions/48425702/group-rows-after-applying-user-filter-in-power-bi?utm_m...

Does anyone know how to do this?  If I add the date picker it doesn't seem to change anything and I think it might be because there is no date in the data set after it has been grouped.  

Thanks.

[sshikari]

I have a data set that I need to:

1. filter by date and then

2. group by 4 fields. 

I would like to provide a date picker in the visualization that will control the data filtered in step #1.  If I add a date picker right now, it doesn't seem to do anything when I select various dates and I think it is because the date is not one of the 4 grouped by fields.  I think a similar question has been asked: https://stackoverflow.com/questions/48425702/group-rows-after-applying-user-filter-in-power-bi?utm_m...

Thanks.

[edhans]

Why not use a Matrix visual to do this?

Put your 4 grouping columns in the "ROWS" of the matrix, the measures you are summarizing in the "VALUES" section, and then a filter visual on the dates. The matrix will automatically exclude data outside of your date range.

[sshikari]

This is an example in SQL that illustrates this problem.  The goal in Power BI is to drive the "@meetingDate" variable from the Visualization section in Power BI (slicer?).  Simply adding a drop down of meeting dates in Power BI did not work.

drop table #tmpMain
drop table #meetingDates
drop table #tmpCurrent
drop table #tmpPrevious

declare @meetingDate datetime
set @meetingDate = '6/4/2018'

-- POSITION DATA
create table #tmpMain (posdate datetime, val float, strategy varchar(120))
insert into #tmpMain values ('6/4/2018', 10, 'a')
insert into #tmpMain values ('6/4/2018', 20, 'b')
insert into #tmpMain values ('6/4/2018', 30, 'c')
insert into #tmpMain values ('5/2/2018', 40, 'a')
insert into #tmpMain values ('5/2/2018', 50, 'b')
insert into #tmpMain values ('5/2/2018', 60, 'c')
insert into #tmpMain values ('5/2/2018', 70, 'd')
insert into #tmpMain values ('4/11/2018', 80, 'a')
insert into #tmpMain values ('4/11/2018', 90, 'b')

select * from #tmpMain

-- MEETING DATES
create table #meetingDates (meetingdate datetime, prevdate datetime)
insert into #meetingDates values ('6/4/2018', '5/2/2018')
insert into #meetingDates values ('5/2/2018', '4/11/2018')

select * from #meetingDates

-- CURRENT MEETING DATA
select
	main.*,
	m.*
into #tmpCurrent
from
	#tmpMain main
	inner join #meetingDates m on m.meetingdate = main.posdate
where
	m.meetingdate = @meetingDate

-- PREVIOUS MEETING DATA
select
	main.*,
	m.*
into #tmpPrevious
from
	#tmpMain main
	inner join #meetingDates m on m.prevdate = main.posdate
where
	m.meetingdate = @meetingDate

select * from #tmpCurrent
select * from #tmpPrevious

-- RESULT WITH DIFF
select
	curr.posdate as currPosDate,
	prev.posdate as prevPosDate,	
	isnull(curr.strategy, prev.strategy) as strategy,
	sum(curr.val) as currVal,
	sum(prev.val) as prevVal,
	sum(isnull(curr.val, 0) - isnull(prev.val, 0)) as diff
from
	#tmpCurrent curr
	full outer join #tmpPrevious prev on curr.strategy = prev.strategy 
group by
	curr.posdate,
	prev.posdate,
	curr.strategy,
	prev.strategy

[v-yuta-msft]

Hi sshikari,

As a general solution based on your description, you can create a slicer and then create a measure on your table using DAX function like this pattern and check if it can work:

Result =
CALCULATE (
    aggregation,
    ALLEXCEPT (
        Table,
        Table[Column1],
        Table[Column2],
        Table[Column3],
        Table[Column4]
    ),
    Table[Date] = SELECTEDVALUE ( Table[Date] )
)

In addtion, you said "The goal in Power BI is to drive the "@meetingDate" variable from the Visualization section in Power BI (slicer?)"

<-- Could you please clarify more details about your requirment?

Best Regards,

Jimmy Tao

[sshikari · Accepted solution]

Thank you for the suggestions. After digging into this further I think my issue was that in power bi I had not grouped the data by the date. I thought that I could use the slicer to filter the data in a previous step (before the grouping) but it seems that the sliver affects the most recent state of the data. Including the date in the group by exposes it in the most recent state and now I can filter. Thanks for your help.