cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdsumann
New Member

One Fact Table References Date Table and Attribute Tables

I have the following tables:

 

Date(Dimension) <--- Exposure (Fact) ---> Incident (Fact) ---> Inspection (Fact Needing Filter)

 

Sample of Exposure Table Is:

IncidentIDExposure Close 1Exposure Close 2FNOL to First Closure
12341/1/20212/1/202110
5678   
9101121/15/20211/15/202120

 


I have two relationships from exposure to dates that I use USERELATIONSHIP to correctly display according to Exposure Close 1 and Exposure Close 2.

 

Avg FNOL to First Close Days (FC) =
CALCULATE (
    AVERAGE ( Exposure[FNOL to First Closure] ),
    Exposure[Exposure Close 1] <> BLANK (),
    USERELATIONSHIP ( Exposure[Exposure Close 1], Dates[Date] )
)

 

That measure works great. I am now trying to use a legend in a line graph to seperate by sales type in the Inspection table.

 

Incident table

IDColorDate Opened
1234Blue12/10/2020
910112Green12/11/2020
5678Yellow12/15/2020

 

Inspection Table

IncidentIDTypeOrder DateFirst Order Flag
1234InternetSale12/25/20201
1234RetailSale12/26/2020 
5678InternetSale12/20/2020 
5678RetailSale12/19/20201
5678RetailSale12/21/2020 

 

I want to basically filter inspection table to return only values = 1 and then add inspection type as a legend on my line graph. I have tried the below to no avail.

 

Avg FNOL to Last Close Days (LC) =
CALCULATE (
    AVERAGE ( Exposure[FNOL to First Closure] ),
    Exposure[Exposure Close 1] <> BLANK (),
    Inspection[First Order Flag] = 1,
    USERELATIONSHIP ( Exposure[Exposure Close 1], Dates[Date] ),
    USERELATIONSHIP ( Exposure[IncidentID], Incident[id] ),
    USERELATIONSHIP ( Inspection[IncidentID], Incident[id] )
)

1 REPLY 1
v-luwang-msft
Community Support
Community Support

Hi @sdsumann ,

What is the result you want to present?

And could you pls share your pbix file? Remember to remove confident data.

 

Best Regards

Lucien

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!