Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AryaKrish
Frequent Visitor

Single Slicer for Multiple Dates, list all data based date from DateView filter

Problem:

In Fact Table I have 5 different Dates like Imported, Collected , Exported , Arrived , FirstProcess and user wants a single select filter on this from where they will select Filter based on which datetype then on a date slicer they want to select From and To date range based on which the visual(matrix) should display the values.

Data Model 

Star Schema Model with Dim(Import Mode) and Fact table(DirectQuery) 

- DimDate table with single active 1 to many relation with FactTable.ImportedDate and 4 inactive 1 to many relation with FactTable.CollectedDate, FactTable.ExportedDate, FactTable.ArrivedDate, FactTable.FirstProcessDate. 

 

I tried to create a table with Slicer with 2 columns - View and ID (This is sisplayed as Filter from where user can select the ViewDate type)

View                  ID

Import1
Collected2
Exported3
Arrived4
FirstProcess5

Dax Measure

ViewSelection =
Var View_Sel = SELECTEDVALUE(Slicer[ID])
Var Imported = CALCULATE(Max('Facttable'[Imported]) in All(CommonCalendar[Date]),USERELATIONSHIP(CommonCalendar[Date],'Facttable'[Imported]))
Var Collected = CALCULATE(Max('FactTablel'[Collected]) in All(CommonCalendar[Date]),USERELATIONSHIP(CommonCalendar[Date],'FactTable'[Collected]))
....
Return
Int(Switch (View_Sel,1,Imported,  2,Collected,  3,Exported,  4,Arrived,  5,FirstProcess))
Something wrong with my dax - I want to list all dates from common date tiable based on the Slicer.View selected by user.
(This is working on Imported as its a Active Relationship but others are not working as expected) 
Please guide me 

 

2 REPLIES 2
AryaKrish
Frequent Visitor

Thanks @amitchandak for te quick response, but my requirement is to filter based on one of the date selection. Lets say the user selects ImportedDate (column) rest all dates should be "All" and based on the date slicer the visual should show records with ImportedDate as selected. Some user selects CollectedDate(Column) then filter the Fact based on date selected against CollectedDate. 

How do I write a Dax which will allow user to decide - date slicer is applied to date based ColumnA or ColumB or ColumnC ... 

amitchandak
Super User
Super User

@AryaKrish , You have to use the common date table and you will inactive join which you can activate in measure using use relationship

 

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.