Matrix Issue

[EG115]

I am trying to report data at a State and County level with three different values (each value is brought in via it's own table). My issue is that the matrix only recognizes the value at a county level for the first value I add. The second and third value I add is incorrect at the county and state level. In the snip below the "Premium" column is accurate, but the "Loss" and "Exposure" columns are inaccurate. Each table is structured similarly with columns for "state", "county", "premium/loss/exposure".

Any suggestions? 

[v-henryk-mstf · Accepted solution]

Hi @EG115 ,

For this type of problem, the error may be caused by the filtering context in the formula. the ISINSCOPE and ISFILTERED functions are used to determine if the selected field is located in the hierarchy.

just like

M_isinscope = ISINSCOPE('Table'[City])

M_isfiltered = ISFILTERED('Table'[City])

If you want the total corresponding to the current field City to also be True, then change the field to its previous level.

M_isfiltered = ISFILTERED('Table'[Country])

M_isinscope = ISINSCOPE('Table'[Country])

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ALLUREAN]

Hi, @EG115 

Are Loss and Exposure tables are connected at the same way like Premium? It looks like they are not connected to State table and the value is the same.

[EG115]

Here is a snip of the Model page. My only relationships are from the County Codes table connecting the "County" with a one-to-many relationship. Am I thinking about this all wrong? 

Here is a snip of the "Premium" table for reference. The loss and exposure data are identical with the only changes being the "Premium" value changing to either "Loss" or "Exposure".