One Fact Table References Date Table and Attribute Tables

Question

I have the following tables:&nbsp;Date(Dimension) &lt;--- Exposure (Fact) ---&gt; Incident (Fact) ---&gt; Inspection (Fact Needing Filter)&nbsp;Sample of Exposure Table Is:IncidentIDExposure Close 1Exposure Close 2FNOL to First Closure12341/1/20212/1/2021105678&nbsp;&nbsp;&nbsp;9101121/15/20211/15/202120&nbsp;I have two relationships from exposure to dates that I use USERELATIONSHIP to correctly display according to Exposure Close 1 and Exposure Close 2.&nbsp;Avg&nbsp;FNOL&nbsp;to&nbsp;First&nbsp;Close&nbsp;Days&nbsp;(FC)&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;AVERAGE&nbsp;(&nbsp;Exposure[FNOL&nbsp;to&nbsp;First&nbsp;Closure]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;Exposure[Exposure Close 1]&nbsp;&lt;&gt;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;USERELATIONSHIP&nbsp;(&nbsp;Exposure[Exposure Close 1],&nbsp;Dates[Date]&nbsp;))&nbsp;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.&nbsp;Incident tableIDColorDate Opened1234Blue12/10/2020910112Green12/11/20205678Yellow12/15/2020&nbsp;Inspection TableIncidentIDTypeOrder DateFirst Order Flag1234InternetSale12/25/202011234RetailSale12/26/2020&nbsp;5678InternetSale12/20/2020&nbsp;5678RetailSale12/19/202015678RetailSale12/21/2020&nbsp;&nbsp;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.&nbsp;Avg&nbsp;FNOL&nbsp;to&nbsp;Last&nbsp;Close&nbsp;Days&nbsp;(LC)&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;AVERAGE&nbsp;(&nbsp;Exposure[FNOL&nbsp;to&nbsp;First Closure]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;Exposure[Exposure Close 1]&nbsp;&lt;&gt;&nbsp;BLANK&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;Inspection[First Order Flag]&nbsp;=&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;USERELATIONSHIP&nbsp;(&nbsp;Exposure[Exposure Close 1],&nbsp;Dates[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;USERELATIONSHIP&nbsp;(&nbsp;Exposure[IncidentID],&nbsp;Incident[id]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;USERELATIONSHIP&nbsp;(&nbsp;Inspection[IncidentID],&nbsp;Incident[id]&nbsp;))

v-luwang-msft · Answer

Hi&nbsp;sdsumann&nbsp;,
What is the result you want to present?
And could you pls share your pbix file? Remember to remove confident data.
&nbsp;
Best Regards
Lucien

Forum Discussion

One Fact Table References Date Table and Attribute Tables

1 Reply

Related content

UDF with variable table reference and fixed column reference

Two Attribute Fields From Same Table in a Table Vis _ Second Attribute is Blank

using a slicer to control which attributes a table is filter by

dynamic reference line

Order attributes

IncidentID	Exposure Close 1	Exposure Close 2	FNOL to First Closure
1234	1/1/2021	2/1/2021	10
5678
910112	1/15/2021	1/15/2021	20

ID	Color	Date Opened
1234	Blue	12/10/2020
910112	Green	12/11/2020
5678	Yellow	12/15/2020

IncidentID	Type	Order Date	First Order Flag
1234	InternetSale	12/25/2020	1
1234	RetailSale	12/26/2020
5678	InternetSale	12/20/2020
5678	RetailSale	12/19/2020	1
5678	RetailSale	12/21/2020