Relationship

[Boopep]

I want to display the population of Municipality in the Sum of Population Card. But every time i click a certain municipality it only diplays the population of a province were that municipality belongs.

My relationship is this. 

I have a Municipality Relationship and Province Relationship for both table.

Here is my DAX Measure in getting the % of Registered to Population. I also want this Card to display the % of Registered based only to the population of the Selected Province or Municipality. So far the Province work but the municipality does not.

% Registered to Population =

DIVIDE(

    CALCULATE(SUM('Konsulta Reg'[REG COUNT])),

    CALCULATE(SUM('POP'[POPULATION]))

)

Hope you can help me.

[rohit1991 · Accepted solution]

Hi @Boopep,

When you select a Province, the Population card is only showing the population for municipalities that exist in your Registration table, not the full province total. This happens because the active relationship between Municipality and Registration is filtering out any municipalities not present in Registration.

You need to adjust your DAX so that at the Province level, it ignores the Registration table filter and returns the full population for the selected province. This is a standard Power BI pattern using REMOVEFILTERS to clear unwanted context.

Try using this measure for your Population Card:

Population Card =
IF(
    ISFILTERED('Municipality'[Municipality]),
    SUM('POP'[POPULATION]), // When a Municipality is selected
    CALCULATE(
        SUM('POP'[POPULATION]),
        REMOVEFILTERS('Konstula Reg')
    ) // When only a Province is selected
)

If a Municipality is selected, you get just that value. If a Province is selected, you get the full Province population even if some municipalities aren’t in Registration.

Make sure your Province and Municipality tables are both connected with single-direction relationships to your fact tables. For even more flexibility, you can use HASONEVALUE or ISINSCOPE if you want to handle more complex

[v-karpurapud]

Hi @Boopep 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved? If not, please provide detailed information so we can better assist you.

Thank you .

[v-karpurapud]

Hi @Boopep 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.


Thank You.

[v-karpurapud]

Hi @Boopep 

We have not yet heard back from you about whether our response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

[v-karpurapud · Accepted solution]

Hi @Boopep 

Thank you for contacting the Microsoft Fabric Community Forum.

To address the relationship issue between data models, I have created a sample .pbix file to illustrate a possible solution. While the structure may not exactly match your dataset, the example demonstrates the logic using calculated columns and DAX to achieve the intended result.

The solution uses two dimension tables: one for Provinces, listing unique Province names, and another for Municipalities, listing unique Municipality names along with their Provinces. The Province Dimension links to the Municipality Dimension via the Province column, and the Municipality Dimension connects to both the Population and Registration tables through the Municipality column, using one-to-many, single-directional relationships.
 

I have included the .pbix file to show this method. Please take a look and let us know if this approach meets your needs or if further adjustments are required for your specific data model.

Regards,

Karpurapu D,

Microsoft Fabric Community Support Team.

 

[rohit1991 · Accepted solution]

Hi @Boopep,

When you select a Province, the Population card is only showing the population for municipalities that exist in your Registration table, not the full province total. This happens because the active relationship between Municipality and Registration is filtering out any municipalities not present in Registration.

You need to adjust your DAX so that at the Province level, it ignores the Registration table filter and returns the full population for the selected province. This is a standard Power BI pattern using REMOVEFILTERS to clear unwanted context.

Try using this measure for your Population Card:

Population Card =
IF(
    ISFILTERED('Municipality'[Municipality]),
    SUM('POP'[POPULATION]), // When a Municipality is selected
    CALCULATE(
        SUM('POP'[POPULATION]),
        REMOVEFILTERS('Konstula Reg')
    ) // When only a Province is selected
)

If a Municipality is selected, you get just that value. If a Province is selected, you get the full Province population even if some municipalities aren’t in Registration.

Make sure your Province and Municipality tables are both connected with single-direction relationships to your fact tables. For even more flexibility, you can use HASONEVALUE or ISINSCOPE if you want to handle more complex

[Boopep]

I did try to follow your advice. Yes it solved the error on the Municipality Level... it displays the correct % if i select the specific Municipality.... the problem now is it does not display the overall value in the Population.... Since not all Municipality are present in the Konsulta Reg table. When you select the Province, the Population Card only adds those Municipalities that are present in the Konsulta Reg table, not the entire population.

Here is the current relationship 

And  i use the DAX Measure you suggested.

[rohit1991]

Hi @Boopep 

That’s happening because Power BI is only summing up population for municipalities that have matching records in the Konsulta Reg table. So when you select a province, the card doesn’t show the full population just the sum for whatever municipalities exist in your reg table.

To fix that, you need to make sure the population measure doesn’t get filtered by the reg table when you're at the province level.

Try this :

Population =
VAR SelectedMunicipality = SELECTEDVALUE('Konsulta Reg'[Municipality])
VAR SelectedProvince = SELECTEDVALUE('Konsulta Reg'[Province])
RETURN
IF(
NOT ISBLANK(SelectedMunicipality),
CALCULATE(SUM('POP'[POPULATION]), 'POP'[Municipality] = SelectedMunicipality),
CALCULATE(SUM('POP'[POPULATION]), REMOVEFILTERS('Konsulta Reg'), 'POP'[Province] = SelectedProvince)
)

That REMOVEFILTERS('Konsulta Reg') part makes sure the population doesn’t get filtered down by what’s available in your reg data so you get the full province value.

[danextian]

Hi @Boopep 

Just based on the image, it appears to be because your active relationship is bidirectional. This is jsut based on the image - there may be additional factors involved.

By the way, CALCULATE is redundant in this measure. Measures are implicitly wrapped in CALCULATE in most cases.

% Registered to Population =
DIVIDE(
    CALCULATE(SUM('Konsulta Reg'[REG COUNT])),
    CALCULATE(SUM('POP'[POPULATION]))
)

[Boopep]

So how should the DAX measure be written.