I need an advice on how I can achieve the following. I have followed some of the suggested workarounds mentioned in the past post but still not able to get the desired result.
I have multiple fact tables in a Claim system that I want to blend together to get certain facts from these fact table in a single worksheet. For e.g. let's say I have the following two Fact tables with key fields (partial) listed below. For ease of reporting in the prior system the dimensions from the member fact table are also present in the Claim table (In the example below it is MemberId, Status and Category)
Member - ClientID, MonthID, MemberID, Status, Category, .....
Claim - ClientID, MonthID, MemberID, Status, Category, ClaimNumber, Paid Amount, .....
For a given Member there can be 0 or more claim record. We could also have situation where there is a member in Claim table without corresponding member record in the Member table. In this case both the member record is identified with ID as -1 in the claim table.
I need to have Member as the Primary Data source and Claim as Secondary Data Source. These two Data sources will be blended on the MonthID field . I want to filter both the tables independently using Status and Category and report the Claim paid amount and number of eligible member by monthID. I don't want to blend the two source on the filtered field even though they are common fields in the two tables because of how the information is to be reported . I have tried both using Parameter and Quick Filter as suggested in the earlier posts here but only the Primary data source gets filtered using either approach. The secondary data source (Claim in the above example) is not getting filtered. How can I filter the secondary data source on the same filtered field as the Primary data source? The only approach that has worked so far is to use the filtered field as Data source filter. But I cannot use that approach because the filter field can vary on client to client basis. I want the measures from the two table to be reported in a single worksheet so cannot use two separate worksheet. Can this be done? I am using Tableau 10.1 version.
I have attached a packaged workbook with dummy data and limited fields. As you can see the Claim Paid amount does not change regardless of the status selected (both using as a quick filter or as a parameter)
Datablend with Sec Filter.twbx 30.7 KB