Filtering Issue

[KL008]

I have an issue with the relationships in my pwoerbi dashboard. The situation is that I have a table called DimRiskCatalogue. This has two columns of interest [NAme] [ID}. It basically just lists roughly 20 categories of Risk that we have. Then I have another table that is called DimRisk. It has the following columns of interest: [Guid] and [RiskCatalogueID]. This gives the instances of all our specific risks that we have, and they are linked to DimRiskCatalogue via a many to one relationship between RiskCatalogueID and ID. 

Then I have a table called Control Relations, this has a few interesting columns/measures. [LinkedRisks], and the measure:
TotalKeyControls2 = 
CALCULATE(
    COUNTROWS(
        FILTER(
            ALLSELECTED('DimControl'),
            COUNTROWS(
                FILTER(
                    RELATEDTABLE('ControlRelations'),
                    'ControlRelations'[IsKeyControl] = TRUE()
                )
            ) > 0
        )
    )
)

It is linked via a many to one relationship from LinkedRisks to Guid (in the table DimRisk)

However, the situation is that currently when I put my measure into a card and try and filter it on [Name] from DimRiskCatalogue, it gives (Blank). Why is that

[Anonymous]

Hi @KL008 ,

1. That may be the crux of the matter.

The calculation here will be done on allselected('dimcontrol'), which will receive external filtering derived from all columns on 'dimcontrol', and you need to take into account that in some cases the empty set is returned.

ControlRelations:

DimControl:

Example data returns the case of the empty set:

If you don't need to consider external filtering from 'DimControl', consider replacing ALLSELECTED with ALL.

2.  I'm assuming that the crossfilter direction of 'DimControl' and 'DimRiskCatalogue' has been set to 'Both', which makes the DimRiskCatalogue' [Name] filter can be passed through 'DimControl' to 'ControlRelations'. 

The possibility of returning an empty set after stacking filters should likewise be taken into account.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[KL008]

Thanks for the reply!
Two things, I need to filter externally, and secondly, there is no relationship bewteen DimControl and DimRiskCatalogue, you can see the relationships below

[ToddChitt]

Any chance you can share some sample data, and a screenshot of the tables in question, with the relationship?

[KL008]

Hey! Sure thing, as follows:
ControlRelations table:

ControlGuid	IsKeyControl	OrganizationGuid	LinkedRisks	Current Date
1	TRUE	A	Z	21-01-2025
1	TRUE	A	Y	21-01-2025
1	TRUE	A	X	21-01-2025
2	FALSE	B	W	21-01-2025
3	TRUE	C	Z	21-01-2025
3	TRUE	C	Y	21-01-2025
1	FALSE	A	Z	21-02-2025
1	FALSE	A	X	21-02-2025
1	FALSE	A	Y	21-02-2025
2	FALSE	B	W	21-02-2025
3	TRUE	C	Z	21-02-2025
3	TRUE	C	Y	21-02-2025

DimRisk Table

RiskGuid	RiskCatalogueID
Z	1
Y	2
X	3
W	4

RiskCatalogue Table

ID	Name
1	Risk1
2	Risk2
3	Risk3
4	Risk4

Here are all the relations: