User Profile
D_PBI
Post Partisan
Joined 6 years ago
User Widgets
Contributions
What visual to show timeframe of events?
Hi, I would like to ask for your suggestions on choosing a Power BI visual to show events in chronological order. I have the following columns: Event (contains values like 'Buying a laptop' or 'selling a car', etc..) Event Date (the date the event happens) Event Reference (a Event code) Event Name (a title for the Event) Event Link (a hyperlink to the Event documentation) Event Contact ID (GUID for the Event Contact) Event Contact (name of the Event Contact) Event Contact Involvement (is the Contact acting as a Buyer or Seller) My aim is for a report user to select a Event Contact GUID from a list/search box visual which will show the list of Events in chronological order for that Contact. It is this visual to use that I am asking for your suggestions on. It would be nice if the Event, at its Date point, is able to pop-up/show the Events details (like Event Name, Reference, etc.. and maybe the option to click on the Hyperlink that opens the record). Is there a Power BI visual that can achieve this? Is there a Power BI report online that presents the same that I can view to take some ideas? Thanks.Re: Can I filter the 1st fact table which then filters a 2nd fact table?
kushanNa - hi again. Unfortunately, what I thought was working, isn't. May I trouble you to view the below DAX measure. It's the DAX measure I'm using to try and achieve my aim. It has slightly chanfged since your help as I needed to expand on it to incorporate my wider need. ------------------------------------------------- ------------------------------------------------- ShowRow = VAR CurrentPage = SELECTEDVALUE( 'Budget Forecast'[Column to filter through] ) VAR CurrentAAgreementPostDealManager = SELECTEDVALUE( 'Budget Forecast'[oagreement.Agreement / Post-Deal Manager] ) VAR CurrentAAgreementStatus = SELECTEDVALUE( 'Budget Forecast'[oagreement.Agreement Status] ) VAR CurrentAAgreementType = SELECTEDVALUE( 'Budget Forecast'[oagreement.Agreement Type] ) VAR CurrentOAccountType = SELECTEDVALUE( 'Budget Forecast'[oobligation.Account Type] ) VAR CurrentOType = SELECTEDVALUE( 'Budget Forecast'[oobligation.Type] ) VAR CurrentOTermType = SELECTEDVALUE( 'Budget Forecast'[oobligation.Term Type] ) VAR CurrentORStatusReason = SELECTEDVALUE( 'Budget Forecast'[oobligationreminders.Status Reason] ) VAR CurrentCCaseStatus = SELECTEDVALUE( 'Budget Forecast'[ocase.Case Status] ) VAR CurrentCCaseType = SELECTEDVALUE( 'Budget Forecast'[ocase.Case Type] ) -------------------------------------------------------------------------------------------------------- -- DETERMINE IF SLICERS HAVE BEEN FILTERED -------------------------------------------------------------------------------------------------------- VAR Is_TTOAccountType_filtered = ISFILTERED( 'TT O Account Type'[oobligation.Account Type] ) VAR Is_TTAAgreementType_filtered = ISFILTERED( 'TT A Agreement Type'[oagreement.Agreement Type] ) VAR Is_TTAAgreementPostDealManager_filtered = ISFILTERED( 'TT A Agreement / Post-Deal Manager'[oagreement.Agreement / Post-Deal Manager] ) VAR Is_TTOType_filtered = ISFILTERED( 'TT O Type'[oobligation.Type] ) VAR Is_TTORStatusReason_filtered = ISFILTERED( 'TT OR Status Reason'[oobligationreminders.Status Reason] ) --VAR Is_TTOReferenceNumber_filtered = ISFILTERED( 'TT O Reference Number'[oobligation.Reference Number] ) VAR Is_MilestonesOType_filtered = ISFILTERED( 'Milestones O Type'[oobligation.Type] ) VAR Is_MilestonesOTermType_filtered = ISFILTERED( 'Milestones O Term Type'[oobligation.Term Type] ) --VAR Is_MilestonesIsObligationEndDateNull_filtered = ISFILTERED( 'Milestones Is Obligation End Date null?'[Is Obligation End Date null?] ) VAR Is_MilestonesCCaseStatus_filtered = ISFILTERED( 'Milestones C Case Status'[ocase.Case Status] ) VAR Is_MilestonesAAgreementStatus_filtered = ISFILTERED( 'Milestones A Agreement Status'[oagreement.Agreement Status] ) --VAR Is_MilestonesOReferenceNumber_filtered = ISFILTERED( 'Milestones O Reference Number'[oobligation.Reference Number] ) VAR Is_RoyaltiesOType_filtered = ISFILTERED( 'Royalties O Type'[oobligation.Type] ) VAR Is_RoyaltiesOTermType_filtered = ISFILTERED( 'Royalties O Term Type'[oobligation.Term Type] ) VAR Is_RoyaltiesAAgreementType_filtered = ISFILTERED( 'Royalties A Agreement Type'[oagreement.Agreement Type] ) VAR Is_RoyaltiesAAgreementStatus_filtered = ISFILTERED( 'Royalties A Agreement Status'[oagreement.Agreement Status] ) VAR Is_RoyaltiesCCaseType_filtered = ISFILTERED( 'Royalties C Case Type'[ocase.Case Type] ) VAR Is_RoyaltiesAAgreementPostDealManager_filtered = ISFILTERED( 'Royalties A Agreement / Post-Deal Manager'[oagreement.Agreement / Post-Deal Manager] ) --VAR Is_RoyaltiesOReferenceNumber_filtered = ISFILTERED( 'Royalties O Reference Number'[oobligation.Reference Number] ) --VAR Is_RoyaltiesAReferenceNumber_filtered = ISFILTERED( 'Royalties A Reference Number'[oagreement.Reference Number] ) -------------------------------------------------------------------------------------------------------- -- RETURN RESULT BASED ON CURRENT PAGE CONTEXT -------------------------------------------------------------------------------------------------------- RETURN SWITCH( CurrentPage, "TT", IF( ( NOT( Is_TTOAccountType_filtered ) || CurrentOAccountType IN VALUES( 'TT O Account Type'[oobligation.Account Type] ) ) && ( NOT( Is_TTAAgreementType_filtered ) || CurrentAAgreementType IN VALUES( 'TT A Agreement Type'[oagreement.Agreement Type] ) ) && ( NOT( Is_TTAAgreementPostDealManager_filtered ) || CurrentAAgreementPostDealManager IN VALUES( 'TT A Agreement / Post-Deal Manager'[oagreement.Agreement / Post-Deal Manager] ) ) && ( NOT( Is_TTOType_filtered ) || CurrentOType IN VALUES( 'TT O Type'[oobligation.Type] ) ) && ( NOT( Is_TTORStatusReason_filtered ) || CurrentORStatusReason IN VALUES( 'TT OR Status Reason'[oobligationreminders.Status Reason] ) ), 1, 0 ), "Milestones", IF( ( NOT( Is_MilestonesOType_filtered ) || CurrentOType IN VALUES( 'Milestones O Type'[oobligation.Type] ) ) && ( NOT( Is_MilestonesOTermType_filtered ) || CurrentOTermType IN VALUES( 'Milestones O Term Type'[oobligation.Term Type] ) ) && ( NOT( Is_MilestonesCCaseStatus_filtered ) || CurrentCCaseStatus IN VALUES( 'Milestones C Case Status'[ocase.Case Status] ) ) && ( NOT( Is_MilestonesAAgreementStatus_filtered ) || CurrentAAgreementStatus IN VALUES( 'Milestones A Agreement Status'[oagreement.Agreement Status] ) ), 1, 0 ), "Royalties", IF( ( NOT( Is_RoyaltiesOType_filtered ) || CurrentOType IN VALUES( 'Royalties O Type'[oobligation.Type] ) ) && ( NOT( Is_RoyaltiesOTermType_filtered ) || CurrentOTermType IN VALUES( 'Royalties O Term Type'[oobligation.Term Type] ) ) && ( NOT( Is_RoyaltiesAAgreementType_filtered ) || CurrentAAgreementType IN VALUES( 'Royalties A Agreement Type'[oagreement.Agreement Type] ) ) && ( NOT( Is_RoyaltiesAAgreementStatus_filtered ) || CurrentAAgreementStatus IN VALUES( 'Royalties A Agreement Status'[oagreement.Agreement Status] ) ) && ( NOT( Is_RoyaltiesCCaseType_filtered ) || CurrentCCaseType IN VALUES( 'Royalties C Case Type'[ocase.Case Type] ) ) && ( NOT( Is_RoyaltiesAAgreementPostDealManager_filtered ) || CurrentAAgreementPostDealManager IN VALUES( 'Royalties A Agreement / Post-Deal Manager'[oagreement.Agreement / Post-Deal Manager] ) ), 1, 0 ), 0 ) ---------------------------------------------------------------------- ---------------------------------------------------------------------- The relationship model is below. To recap on the need. I have a TT page, a Milestones page, and a Royalties page. These separate pages have their own individual slicers - you can see this through the relationships. Each page will show the records filtered by their own slicers. All works as expected on this. I then need the head page named Budget Forecast to have a table visual that shows the filtered records from the TT, Milestones, and Royalties pages in an appended form. The fields used in this visual are taken from the Budget Forecast table. The DAX measure shown above is (intended to be) used to flag the records to show in the table visual. This is achieve by adding it to the table filter property and only showing records with 1. If absolutely no filters are applied to, say for example, the TT page then all records from the TT table should show in the Budget Forecast table. This is the same logic for the Milestones and Royalties pages. One last thing. One of the filters on the Milestones page is to include a Start Date range from the user selected start date and later, and is to include nulls too values (records that have blank dates too). I have a DAX measure that achieves this and it is below. It works on the Milestones page but it will also need to work on the Budget Forecast page for the Milestones records. ---------------------------------------------------- ---------------------------------------------------- __Milestones Obligation Start Date to show = VAR _minDate = MIN( 'Milestones O Start Date'[obligation.Start Date] ) VAR _RowsDate = MAX( 'Budget Forecast - Milestones'[obligation.Start Date] ) RETURN IF( OR( _RowsDate >= _minDate, ISBLANK( _RowsDate ) ), 1, 0 ) ------------------------------------------------------- ------------------------------------------------------- The outstanding issue is the Budget Forecast page isn't showing the filtered records. It's showing no records. For example, if I filter the TT page/table to return/show a single record, I would expect that the Budget Forecast page/table to append only that same single record from the filtered TT page. If I remove the measure flag from the filter pane, then all records show. This strikes me that the DAX measure is not flagging/assigning any records with a 1 (If I set the DAX measure flag to 1 in the filter pane then NO records are shown. If I set the DAX measure to 0 in the filter pane then ALL records are shown). If anyone can help that will be great. I'm starting to wonder if it is possible (but I still this it is). Thanks.Re: Can I filter the 1st fact table which then filters a 2nd fact table?
kushanNa - many thanks for taking the time to produce a mock-up report. It's most helpful of you. However, it isn't working as required. I'll provide more clarity below. When configuring your version so the UK Type is filtered to Plastic alone. The UK (fact) table show only Plastic records - which is correct. The US (fact) shows all records - which is correct. However, the Word (fact) table is also filtered to show Plastic records from both fact tables - this isn't correct/wanted. If the US Type filter is not filtered, then I would expect the Word (fact) table to only show Plastic records from the UK (fact) table. When configuring your version so the US Type is filtered to Metal and Wood. The US (fact) table shows only Metal and Wood records - which is correct. The UK (fact) shows all records - which is correct. However, the Word (fact) table is also filtered to show Metal and Wood records from both fact tables - this isn't correct/wanted. If the UK Type filter is not filtered, then I would expect the Word (fact) table to only show Metal and Wood records from the US (fact) table. When configuring your version so the UK Type is filtered to Plastic, and the US Type filter is filtered to Metal and Wood. The UK (fact) table shows only Plastic records - which is correct. The US (fact) shows only Metal and Wood records - which is correct. However, the Word (fact) table shows no records - this isn't correct/wanted. The Word (fact) table should show the Plastic records from the UK (fact) table and the Metal and Wood records from the US (fact) table. How do I achieve this? Please can you amend your report so it reflects me aim? Many thanks.
