Forum Discussion

WinterMist's avatar
WinterMist
Impactful Individual
2 years ago
Solved

Beneficiary Model - Fact Table Does Not Properly Filter Beneficiary Table

Hello Community -   I have 2 models where the "Fact" table has a 1:* relationship with a "Beneficiary" table.   Each Policy ID should only appear in the Fact table once. However, a single Policy...
Need Help
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey WinterMist ,

     

    regarding
    1) use CROSSFILTER( ..., ..., BOTH) to create a dedicated measure instead of changing the cross-filter direction 

    2) I think it will be a great read

    3) I do not think I will spend this time to write about this type of relationships here, maybe I will write an article on my site. But, it is obvious (from my perspective) as the measure makes the second table "explode".

     

    Regards,

    Tom

WinterMist's avatar
WinterMist
Impactful Individual
2 years ago

TomMartens 

 

Thanks again for your time.

 

Here is a link to the PBIX.

https://drive.google.com/file/d/1USKah9XhEV5--ajUYh2ZMccMWlrHeR2Z/view?usp=sharing

 

To replicate the issue:

1) Set "Insured Name" slicer = Insured 1

   - This will correctly isolate 1 row in the first table visual & 4 rows in the second table visual.

   - So far no problems.

 

 

2) Drag the measure [M2 Concat Crops] onto the second table.

 

DESIRED RESULT: The detailed view should continue to show the same applicable 4 rows (per the above screenshot), except that the [M2 Concat Crops] measure should also appear as an additional column, showing "Corn, Soybeans" for all 4 rows.

 

UNDESIRED RESULT: As long as BOTH of the following items are present in the same table visual,

then the problem occurs:

1) 'Beneficiary'[Beneficiary Name]

2) [M2 Concat Crops]

 

If either item is removed from the table visual, then the problem goes away.

The problem is that ALL Beneficiary Names come into the visual, even the Beneficiary Names which have no relationship with "Insured 1"; and all other data is simply repeated for every single Beneficiary Name value.

 

 

Regards,

Nathan

 

P.S.  Minor side question.

- I am familiar with "Fact" & "Dimension" categories of tables.

- But is there a category name for a table that is filtered by the "Fact" table?  I don't even know what to call it.  I wouldn't call it a Fact or a Dimension.