Creating a table in DAX with filtered by page filter and using inactive relationship

[Riyaz999]

I have a complication model that ultimately has an ambiguous relationship back to my Report table.  I was able to make one path an inactive relationship but I am having trouble creating a dax table that uses the inactive relationship in conjunction with a page level filter.

Report

ReportId	ReportName
1	Report1
2	Report2

Metric

MetricId	MetricName
1	Metric1
2	Metric2
3	Metric3
4	Metric4

Station

StationId	StationName
1	Station1
2	Station2
3	Station3

ReportMetric

ReportId	MetricId	Ordinal
1	1	1
1	2	3
1	3	2
2	2	2
2	3	3
2	4	1

ReportStation

ReportId	StationId	Ordinal
1	2	1
1	1	2
1	3	3
2	1	2
2	2	1

FACT

Date	Time	MetricName	StationName	Value
Jan 1, 2025	12:00	Metric1	Station1	x1
Jan 1, 2025	12:00	Metric2	Station1	x2
Jan 1, 2025	12:00	Metric3	Station1	x3
Jan 1, 2025	12:00	Metric1	Station2	x4
Jan 1, 2025	12:00	Metric2	Station2	x5
Jan 1, 2025	12:00	Metric3	Station2	x6
Jan 1, 2025	12:00	Metric1	Station3	x7
Jan 1, 2025	12:00	Metric2	Station3	x8
Jan 1, 2025	12:00	Metric3	Station3	x9
Jan 1, 2025	12:00	Metric2	Station1	x10
Jan 1, 2025	12:00	Metric3	Station1	x11
Jan 1, 2025	12:00	Metric4	Station1	x12
Jan 1, 2025	12:00	Metric2	Station2	x13
Jan 1, 2025	12:00	Metric3	Station2	x14
Jan 1, 2025	12:00	Metric4	Station2	x15

Relationships

Metric 1 to Many ReportMetric

Report 1 to Many ReportMetric

Station 1 to Manay ReportStation

Report 1 to Many ReportStation

Since the ReportMetric table a many to many relationship to the FACT table and likewise the ReportStation (MetricIds 2 & 3 appear multiple times on both sides of the relationship), I need to create a relationship in between these two tables.  This was done with a dax table.  It also makes it easier to join my FACT table as I have included the MetricName field.

ReportMetricFilteredbySingleRpt =

CALCULATETABLE(

    ADDCOLUMNS(

        'ReportMetric',

        "MetricName", LOOKUPVALUE('Metric'[MetricName], 'Metric'[MetricId],'ReportMetric'[MetricId])

    ),

    FILTER(ReportMetric, 'ReportMetric'[ReportId] = [mSeletectedReportId])

)

I also used the following measures:

mSeletectedReportId = CALCULATE(MAX('Report'[ReportId]), FILTER('Report', 'Report'[ReportName] = [mSeletectedReport]))

mSeletectedReport = SELECTEDVALUE('Report'[ReportName])

as well as page level filters on each report page.  i.e., on page 1 I set the filter to Report1 and on page 2 I set it to Report2.  

Relationships

Metric 1 to Many ReportMetricFilteredbySingleRpt

Report 1 to Many ReportMetricFilteredbySingleRpt

ReportMetricFilteredbySingleRpt Many to Many to FACT

I had to make this last relationship many to many for power bi to accept it but since I have a page filter that limits the results, I don't actually have a many to many relationship at the data level.

This solution works for the Metrics and I can pull in the ordinal value for a given Report to Metric relationship such that my Metrics appear on the visuals in question in the prescribed order.

The issue is that I can't apply the same logic on the ReportStation side because it results in two paths to Report (ambiguous path to Reports error)

i.e., 

Station 1 to Many ReportStationFilteredbySingleRpt

Report 1 to Many ReportStationFilteredbySingleRpt

ReportStationFilteredbySingleRpt Many to Many to FACT

I have tried many different approaches in DAX to create a table for ReportStationFilteredbySingleReport but to no avail and the result is that I have Stations appearing in report where I don't want to see them as well as duplicate entries where a Station appears more than once.

One additional point to mention, is to resolve the duplicate paths to Report, I had to make the relationship from ReportStationFilteredbyReport to Report an inactive relationship.

Essentially, I need to use the inactive relationship:

CALCULATETABLE(

    'Report',

    USERELATIONSHIP('Report'[ReportId], 'ReportStation'[ReportId])

)

noting that I have the page filter for 'Report'[ReportName] set to Report1 on tab/page1

as well as filter the results to only include ReportId = 1 on page 1.

VAR RptId = LOOKUPVALUE('Report'[ReportId], 'Report'[ReportName], SELECTEDVALUE('Report'[ReportName]))

RETURN

FILTER('ReportStation',
   'ReportStation'[Reportid]=RptId

)

If I hardcode RptId = 1, it works but then I can't use this measure on page 2.  I actually have 10 report pages with 20+ Metrics and 10 Stations.  There is a lot of overlap in terms of which Stations and Metric appear on which reports and the order is not always the same. 

I am looking for help with a generic dax statement for ReportStationFilteredbyReport to use the page level filter value and the inactive relationship to return a list of Stations that would appear on the given report page and the order, given by the ordinal field, in which they would appear.

Thanks in advance,

[V-yubandi-msft]

Hi @Riyaz999 ,

I wanted to check in regarding your issue. Has it been resolved, or do you need any further information. Let me know if you’d like more details.

Thanks.

[V-yubandi-msft]

Hi @Riyaz999 ,
May I know if your issue has been resolved, or if you need any additional assistance? Please feel free to let us know.

Thanks.

[V-yubandi-msft]

Hi @Riyaz999 ,

Thank you for reaching out to the Microsoft Fabric Community. The ambiguity occurs because Power BI recognizes multiple paths from FACT to Report, particularly when both ReportStationFilteredbySingleRpt and ReportMetricFilteredbySingleRpt are linked to Report. Even if one path is inactive, using both in the same visual or calculation can still cause ambiguity errors.

To resolve this, you can try

1. Apply USERELATIONSHIP() within CALCULATE() to activate the necessary relationship.

2. Avoid joining both filtered tables directly to Report in the same visual or measure.

3. Separate the logic for ReportStationFilteredbySingleRpt using a calculated table that follows the page-level filter and activates the appropriate relationship.

Helpful Reference: 
Active vs inactive relationship guidance - Power BI | Microsoft Learn
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Regards,
Yugandhar.

[Riyaz999]

Hi @V-yubandi-msft,

I guess my issue is I don't know how to resolve my issue using your suggestion option #1.  My latest attempt is:

ReportStationFilteredbySingleRpt =

CALCULATETABLE(

    CALCULATETABLE(

        ADDCOLUMNS(

            'ReportStation',

            "StationName", LOOKUPVALUE('Station'[StationName], 'Station'[StationId],'ReportStation'[StationId])

        ),

        USERELATIONSHIP('ReportStation'[ReportId], 'Report'[ReportId]),

    ),

    'ReportStation'[ReportId] = [mSeletectedReportId]

)

The version that returns the duplicates is:

ReportStationFilteredbySingleRpt =

CALCULATETABLE(

    ADDCOLUMNS(

        'ReportStation',

        "StationName", LOOKUPVALUE('Station'[StationName], 'Station'[StationId],'ReportStation'[StationId])

    ),

    FILTER('ReportStation', 'ReportStation'[ReportId] = [mSeletectedReportId])

)

[V-yubandi-msft]

Hi @Riyaz999 ,

Thank you for sharing your latest attempt you're very close. The main issue is that you're still filtering  ReportStation [ReportId] directly, which bypasses the inactive relationship you're aiming to activate with USERELATIONSHIP(). Additionally, it's not necessary to nest CALCULATETABLE() in this case.

Try This Version

ReportStationFilteredbySingleRpt =
CALCULATETABLE(
   ADDCOLUMNS(
       'ReportStation',
       "StationName", LOOKUPVALUE('Station'[StationName], 'Station'[StationId], 'ReportStation'[StationId])
   ),
   USERELATIONSHIP('ReportStation'[ReportId], 'Report'[ReportId]),
   'Report'[ReportName] = SELECTEDVALUE('Report'[ReportName])
)

Please give it a try and let me know how it turns out.

Regards,
Yugandhar.

[Riyaz999]

Hello Yugandhar,

thanks for your response.  Unfortunately, this didn't work for me.  I got the following circular dependency error:

It may be useful to see my response to FBergamaschi as to why this circular dependency exists:
https://community.fabric.microsoft.com/t5/Desktop/Creating-a-table-in-DAX-with-filtered-by-page-filt...

Thanks in advance,

R

[V-yubandi-msft]

Thanks again for the detailed follow up and for linking your explanation to FBergamaschi that context is super helpful.

Circular dependency errors like the one you're seeing often occur when a calculated table references filters or relationships that indirectly depend on itself. In your case, using  Report'[Report Name]= Selectedvalue.. inside a calculated table tied to Report  via an inactive relationship can trigger that loop.

Suggested pattern to try

While I haven’t been able to test this DAX directly, here’s a pattern that may help break the dependency.

VAR RptId = CALCULATE(MAX('Report'[ReportId]))
RETURN
CALCULATETABLE(
    ADDCOLUMNS(
        'ReportStation',
        "StationName", LOOKUPVALUE('Station'[StationName], 'Station'[StationId], 'ReportStation'[StationId]),
        "Ordinal", 'ReportStation'[Ordinal]
    ),
    USERELATIONSHIP('ReportStation'[ReportId], 'Report'[ReportId]),
    'ReportStation'[ReportId] = RptId
)

This version avoids referencing Report Name  directly and instead uses a resolved Report ID  variable, which may help avoid the circular dependency.

Hope this helps..

[Riyaz999]

Hi again @V-yubandi-msft,
Thanks so much for the suggestion.  Unfortunately, I still get the circular dependency with this approach.

Regards,

R 

[V-yubandi-msft · Accepted solution]

Hello @Riyaz999 ,

Thanks for your patience and for trying out different solutions. I really appreciate your effort.

Since the circular dependency appears to be caused by the calculated table referencing the Report table, you might want to move the logic to the measure level instead.

Consider using USERELATIONSHIP() dynamically within a measure and applying it as a visual level filter. This approach can help you avoid creating a separate filtered table and prevent the circular reference issue.

Let me know if this helps. I'm happy to keep working on it with you.

Regards,
Yugandhar.

[V-yubandi-msft]

Hi @Riyaz999 ,
Has your issue been resolved, or do you need any additional information to move forward?

Thanks

[Riyaz999]

P.S. I think it is easier to visualize looking at my response to: FBergamaschi.

[FBergamaschi]

Thanks for the detailed post

Can you show images of the issue and what you want to achieve? Images help understand faster

Thanks

[Riyaz999]

Model:

In matrix visuals above, which appear on different report pages, I have the following:

Rows:

StationName (ReportStationFilteredbySingleRpt)

Date (FACT)

Time (FACT)

Columns:

MetricName (ReportMetricFilteredbySingleRpt)

Values:

mRoundedValue

mRoundedValue =

VAR Metric = SELECTEDVALUE('FACT'[Metric])

VAR Precision = LOOKUPVALUE('Metric'[Precision], 'Metric'[MetricName], Metric)

VAR PrecFormat = IF(VALUE(Precision) > 0, "." & REPT("0", Precision), "")

RETURN IF(ISBLANK(Precision), 'FACT'[Value], FORMAT('FACT'[Value], "0" & PrecFormat))

MetricName doesn't have any duplication or undesired Metrics shown on each visual because I have an active relationship between ReportMetricFilteredbySingleReport and Report.  Since there is an inactive relationship between ReportStationFilteredbySingleReport and Report, I need to Calculate that table by both applying the page level filter for the selected report and utilizing the use of that inactive relationship between ReportStationFilteredbySingleReport and Report.

I hope this clarifies.