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