CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
TStewartFan
TStewartFan

Reputation: 17

Power BI Visual Filter Not Filtering All Other Visuals

I have a date table that is used to populate a date slicer. The date slicer filters all other filters on the page but one. The one it doesn't filter has a one-to-many relationship back to the date table.

However, when I use the data from table two as an axis (Date and Hour) it actually displays all date/hours from the entire table but doesn't restrict the date/hour range to that of the parent date table (table one). Thoughts on how I can achieve this without using merge tables (preferable in DAX)?

In SQL Server I would do the following to achieve this output:

select fc1.calendardatewithtime, totaltable.total
from FiscalCalendarWithTime fc1
cross apply (
select top(1) count(distinct id) total
from ActionDetail ad1
where ad1.upgraded_on=fc1.calendardatewithtime and ad1.status=3
)as totaltable
where exists ( select 1 from dbo.FiscalCalendarTable fc2 where fc2.calendardate=fc1.calendardate and fc2.fiscalweek=1 )
order by fc1.calendardatewithtime asc;

Where calendardatewithtime is the date with time field that I would use as the axis and totaltable.total is the value I would display as the graph total.

My Date Slicer code:

SpecialDateDropdown = 
VAR _datetable = FiscalDGCalendar
VAR _today = TODAY()
VAR _yesterday = TODAY()-1
VAR CurrentFiscalWeek = calculate(min(FiscalCalendar[fiscal_week]),filter(FiscalCalendar,format(now(),"mm/dd/yyyy")=format(FiscalCalendar[fiscal_date],"mm/dd/yyyy")))
VAR CurrentFiscalPeriod = calculate(min(FiscalCalendar[fiscal_period]),filter(FiscalCalendar,format(now(),"mm/dd/yyyy")=format(FiscalCalendar[fiscal_date],"mm/dd/yyyy")))
VAR CurrentFiscalQuarter = calculate(min(FiscalCalendar[fiscal_quarter]),filter(FiscalCalendar,format(now(),"mm/dd/yyyy")=format(FiscalCalendar[fiscal_date],"mm/dd/yyyy")))
VAR CurrentFiscalYear = calculate(min(FiscalCalendar[fiscal_year]),filter(FiscalCalendar,format(now(),"mm/dd/yyyy")=format(FiscalCalendar[fiscal_date],"mm/dd/yyyy")))
RETURN UNION(
    ADDCOLUMNS(FILTER(_datetable,[fiscal_date]=_today),"Period","Today","Order",1),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_date]=_yesterday),"Period","Yesterday","Order",2),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear&&[fiscal_week]=CurrentFiscalWeek),"Period","Current Fiscal Week","Order",3),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear&&[fiscal_week]=CurrentFiscalWeek-1),"Period","Prior Fiscal Week","Order",4),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear&&[fiscal_period]=CurrentFiscalPeriod),"Period","Current Fiscal Month","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear&&[fiscal_period]=CurrentFiscalPeriod-1),"Period","Prior Fiscal Month","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear&&[fiscal_quarter]=CurrentFiscalQuarter),"Period","Current Fiscal Quarter","Order",7),
    ADDCOLUMNS(FILTER(_datetable,[fiscal_year]=CurrentFiscalYear),"Period","Current Fiscal Year","Order",8),
    ADDCOLUMNS(_datetable,"Period","Custom Date Range","Order",9)
)

And this is how I am gathering my totals so the only piece I am missing is how to visually display only a specific date range:

CALCULATE(
IF(
    ISBLANK(DISTINCTCOUNT(ActionHistoryDetail[id])),0,DISTINCTCOUNT(ActionHistoryDetail[id])
),ActionHistoryDetail[status]=3,USERELATIONSHIP(FiscalCalendar[fiscal_date],fiscalcalendarwithtime[CalendarDate]),USERELATIONSHIP(fiscalcalendarwithtime[CalendarDateWithTime],ActionHistoryDetail[upgraded_on])
)

Upvotes: 0

Views: 5624

Answers (1)

user8078111
user8078111

Reputation: 468

You can group the slicers in a single family, as all the slicers will have the same values(if values are the same). Then you can hide the second slicer behind the first slicer.

  1. As you can see Date Table doesn't filter DATA TABLE as I haven't grouped them yet.

Without Grouping

  1. Now I got to View in the ribbon and select the sync slicers option and then go to Advanced Controls and group both of the slicers as A.

View >> Sync Slicers >> Advanced Options

How to use this

  1. Now as both of the slicers are grouped, when I sleect the Year from the Data Table slicer it filters the DATA TABLE table too.

Ta-da Output

  1. Now just hide the other slicer.

Upvotes: 0

Related Questions