Forum Discussion
Filtering before group by
- 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.
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.
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.strategyHi 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.
