Forum Discussion
Relationship
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
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.
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.
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.