Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soldstatic
Resolver I
Resolver I

Drillthrough on Related Field from another Table

I'mt rying to get this visual to allow me to drillthrough to another page and use a related field as the drillthrough target. IE: FeederAsDesigned appears in two tables, the first table always has a customer served count, so this is the field I use as the basis for the visual. Then I have several measures that 'userelationship' etc to another table that sometimes has events in it if an outage was recorded for those feeders. I have another tab that is based on this second field from the table of events that would allow me to drillthrough to that feeder, except that the only fields being sent to the tab appear to be from the first table (and the other filters on the report).

 

CMI = 
if(isfiltered(ServedByDateFeeder[FeederAsDesigned]) && HASONEVALUE(ServedByDateFeeder[FeederAsDesigned])
    ,calculate(
        if(HASONEVALUe('QualifiedEventList'[BeginYr])
                ,calculate(
                    sum('QualifiedEventList'[CMI])
                    ,USERELATIONSHIP('QualifiedEventList'[EventGeography],'ServiceCenterHierarchy'[ID] )
                )
                ,blank()
                )
        ,CROSSFILTER('EventPremise'[EventListId],'QualifiedEventList'[ID],none)
        ,USERELATIONSHIP(QualifiedEventList[FeederAsDesigned],ServedByDateFeeder[FeederAsDesigned])
        ,CROSSFILTER(QualifiedEventList[FeederAsDesigned],ServedByDateFeeder[FeederAsDesigned],both)
    )
    ,calculate(
    if(HASONEVALUe('QualifiedEventList'[BeginYr])
            ,calculate(
                sum('QualifiedEventList'[CMI])
                ,USERELATIONSHIP('QualifiedEventList'[EventGeography],'ServiceCenterHierarchy'[ID] )
            )
            ,blank()
            )
    ,CROSSFILTER('EventPremise'[EventListId],'QualifiedEventList'[ID],none)
    )
)



Customer Count Feeder = 

CALCULATE(
    SUM('ServedByDateFeeder'[DistinctPremises])
    ,USERELATIONSHIP('ServedByDateFeeder'[FeederAsDesigned],QualifiedEventList[FeederAsDesigned])
    ,USERELATIONSHIP('ServedByDateFeeder'[CALENDAR_DATE],'DateTable'[FOM Date])
    ,CROSSFILTER('DateTable'[CALENDAR_DATE],'QualifiedEventList'[BeginDateOnly],none)
)

FeederRelationshipVisual.pngFeederRelationship.png

2 REPLIES 2
v-xicai
Community Support
Community Support

Hi @soldstatic ,

 

You may delete the secondary relationships, and hold the main relationships which will be used in your formulas, change the Cross filter direction of the relationship among the tables above from Single to Both, which will take all the tables treated as a single table.

 

You can learn more about relationship :https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships.

 

Best Regards,

Amy

 

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

That would automatically filter out rows that don't have matches which I don't want to do. I need to be able to see that Feeder 444 doesn't have any events associated with. If Feeder 444 doesn't come up in the search results etc, it will throw off the user.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.