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 very much for your response.

1 step closer.

 

The REAL MODEL table visual has fields from:

- multiple Dimensions

- the Fact Table

- 1 measure which is a concatenated list of Crops from the Crop dimension.

 

I compared this to the TEST MODEL, which has table visual fields from:

- multiple Dimensions

- the Fact Table

 

I realized the only high level difference is that the TEST MODEL does not have the measure (which concatenates the values of a dimension).

 

As a test, I removed the measure from the table visual and the problem goes away.

So I have determined that the issue only occurs when BOTH of the following are true:

1) The measure is present in the table visual AND

2) At least 1 field from the Beneficiary table is present in the table visual

 

So in the REAL model:

'D Crop'[CropKey] filters 'Fact'[CropKey]

'Fact'[FactPolicyKey] filters 'Fact **** - Other Persons'[FactPolicyKey]

 

 

The purpose of the measure is simply to concatenate the crops for a given row.

For example, Policy # "12345" might have 2 crops (Corn & Soybeans).

IF 'D Crop'[Crop Name] is used, then the table visual displays this 1 Policy # over 2 rows (1 row for each crop).

IF instead, the measure is used, it concatenates "Corn, Soybeans" together so that this data is displayed on only 1 row in the table visual (which is a business requirement).

 

 

Once again, this measure has been working fine in the table visual all along.

It was only when I added a field from the newly added Beneficiary table ("Fact **** - Other Persons) that the table visual breaks.

 

Hoping you are able to see an issue with my measure?

 

Regards,

Nathan

TomMartens's avatar
TomMartens
Super User
2 years ago

Hey WinterMist ,

 

I'm not smart enough to create a solution by staring at blacked-out screenshots and measure text that provides a fix for a realtionship issue, a measure issue, or a combination of both.

 

I can not spend more time, if you are not able to spend time to create a pbix with sample data.

 

Regards,

Tom