Exclude rows that have gaps in a time dimension

Question

Hi,&nbsp;I have the following problem:&nbsp;I have a hierarchy with&nbsp;2 levels (could be more in a real situation):Region and Line:RegionLineR1L1R1L3R2L2R2L4&nbsp;In a fact table I have the following situation:&nbsp;ProdMonthRegionLineUptime10/1/2017R1L11011/1/2017R1L11112/1/2017R1L1121/1/2018R1L1132/1/2018R1L1143/1/2018R1L1154/1/2018R1L1165/1/2018R1L11710/1/2017R2L22011/1/2017R2L22112/1/2017R2L2221/1/2018R2L2232/1/2018R2L2243/1/2018R2L2254/1/2018R2L2265/1/2018R2L22710/1/2017R1L33011/1/2017R1L33112/1/2017R1L3321/1/2018R1L3332/1/2018R1L3343/1/2018R1L3355/1/2018R1L33710/1/2017R2L44011/1/2017R2L44112/1/2017R2L4421/1/2018R2L4432/1/2018R2L4453/1/2018R2L4465/1/2018R2L447&nbsp;I need to calculate 6 month Trends for Uptime measure per Region.But I need to include in the trends (lets suppose Avg(Uptime) just the Lines that have values for all 6 months.In this case the trends for R1 should include just L1 and R2 just L2, because L3 and L4 have missing values for 4/1/2018.How can I could achieve this?&nbsp;The initial month for the trend has to be choose by an slicer.I tried to think in calculated tables, but those are not impacted by a slicer.&nbsp;Thanks for any idea!&nbsp;Regards,&nbsp;Alex

gipiluso · Accepted Answer

leverage on row context you will be able to exclude from calculation the item with missing data.

As example you can use:

CALCULATE(

AVERAGE(Fact[KPI]), FILTER(SUMMARIZE(Fact,Fact[Line] ,"a",DISTINCTCOUNT(Fact[ProdMonth])),[a]=CALCULATE(DISTINCTCOUNT(Fact[ProdMonth]),all(Fact[Line]))

)

Forum Discussion

Exclude rows that have gaps in a time dimension

2 Replies

Related content

Update rows in dimension excluding the surrogate key / MERGE

Card Visual not showing correct values after excluding dimension column

degenerate dimensions

Exclude rows

Measure excluding row context

ProdMonth	Region	Line	Uptime
10/1/2017	R1	L1	10
11/1/2017	R1	L1	11
12/1/2017	R1	L1	12
1/1/2018	R1	L1	13
2/1/2018	R1	L1	14
3/1/2018	R1	L1	15
4/1/2018	R1	L1	16
5/1/2018	R1	L1	17
10/1/2017	R2	L2	20
11/1/2017	R2	L2	21
12/1/2017	R2	L2	22
1/1/2018	R2	L2	23
2/1/2018	R2	L2	24
3/1/2018	R2	L2	25
4/1/2018	R2	L2	26
5/1/2018	R2	L2	27
10/1/2017	R1	L3	30
11/1/2017	R1	L3	31
12/1/2017	R1	L3	32
1/1/2018	R1	L3	33
2/1/2018	R1	L3	34
3/1/2018	R1	L3	35
5/1/2018	R1	L3	37
10/1/2017	R2	L4	40
11/1/2017	R2	L4	41
12/1/2017	R2	L4	42
1/1/2018	R2	L4	43
2/1/2018	R2	L4	45
3/1/2018	R2	L4	46
5/1/2018	R2	L4	47