Forum Discussion

sshikari's avatar
sshikari
Regular Visitor
8 years ago
Solved

Filtering before group by

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...
  • sshikari's avatar
    sshikari
    8 years ago
    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.
sshikari's avatar
sshikari
Regular Visitor
8 years ago

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_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

 

Thanks.

  • sshikari's avatar
    sshikari
    Regular Visitor
    8 years ago

    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's avatar
      v-yuta-msft
      Community Support
      8 years ago

      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's avatar
        sshikari
        Regular Visitor
        8 years ago
        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.
  • edhans's avatar
    edhans
    Community Champion
    8 years ago

    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.