Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm still getting used to Power Bi but am having an issue with trying to create a filter within my report.
I've two columns in "Table A" one to do with start date and another to do with end date (some instances it's null), I've created an if statement using both columns that works based off todays date using Now(), but I'm trying to get this to work as a certain point in time feature i.e. what would the view be like this day last year?
I've added in a Date Dimension table and have linked this table with a both a viewable and hidden relationship.
*** Adding more details to this I'm trying to create a view that will enable me to get the following results***
Database | Results | |||||
Name | Employee Status | Commence date | End date | Date | Active Employees | |
John | Permenant | 01/01/2017 | Null | 07/12/2017 | 1 | |
Paul | Permenant | 16/06/2016 | 07/09/2017 | 07/06/2017 | 2 | |
Mary | Temp | 12/12/2017 | Null |
Any help on this would be appreciated. Thanks
Dear @eamonnde,
Can you show more about your issue? An image/result of your expect/your formular (this is important) or share the .pbix(delete the sensitive data or create dummy data).
Regards,
ManNVSM.
Hi @Anonymous,
I've attached an image of the two tables below.
The issue I have is that I'm trying to use the Commenced_date and Ended_date in Table A together which will in turn impact on the results shown in the various views I have on the report page. I'm trying to get this function to work as a slicer if possible.
The relationship below are;
Visible - 'date'[Date] to 'Table A'[Commenced_date]
Hidden- 'date'[Date] to 'Table A'[ended_date]
Dear @eamonnde,
Between 2 table, just only 1 column accept as Relationship_Key. Sometime you must combine 2 column to make a new Relationship_Key. In your case, just only 1: Date[Date] connect Table A'[Commenced_date]
You should create a new Date table link with another hidden. Using simple code when create new table: EndDate =CALENDAR (DATE (2017, 1, 1), DATE (2018, 12, 31)).
Regards,
ManNVSM.
Hi @Anonymous,
OK, I might be missing something having created the new date table with the following relationships
'date'[Date] to 'Table A'[Commenced_date]
'enddate'[Date] to 'Table A'[ended_date]
How does the creation of this new table enable me to create a slicer that can be used to display specific date related information in my view?
Thanks for the help
eamonnde
Dear @eamonnde,
So, can you tell me your expect in this report? It's seem your report about employee, people use to:
Regards,
ManNVSM.
Hi @Anonymous
What I'm trying to have in place is something like what I've included in the above image with my initial query, where by I'm able to select a Single date and on that date I'm able to identify the number of people who were actively working for X company based off the start date and end date columns.
In excel I could do this with an IF function and i could relate it back to a single cell, using that cell run a nested IF based on a combination of greater thans, less thans and nulls.
Hopefully a bit clearer, thanks again
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |