Single Monthly trend by using 2 Timestamp and 2 ID values from Same Dataset

[Peter_Jeyaraj_I]

Hi Team, 

i have 2 different IDs and 2 different timestamp & trying to show the monthly wise IDs count in same chart. 

See below sample data

ID1	TimeSTamp1	ID2	TimeSTamp2
1	1/4/2018	10	1/20/2018
2	1/4/2018	11	2/20/2018
3	2/8/2018	12	3/20/2018
3	2/8/2018	13	3/20/2018
4	2/8/2018	14	3/20/2018

chart i am looking 

 

[Peter_Jeyaraj_I]

this s the chart i am looking for.. please share your inputs

[v-cherch-msft · Accepted solution]

Hi @Peter_Jeyaraj_I

You may create the relationships as below and use USERELATIONSHIP Function.Here is the article for your reference.

Count_ID1 = DISTINCTCOUNT(Table1[ID1])

Count_ID2 = CALCULATE(DISTINCTCOUNT(Table1[ID2]),USERELATIONSHIP('Calendar'[Date],Table1[TimeSTamp2]))

Regards,

Cherie

[Peter_Jeyaraj_I]

Hi @v-cherch-msft, 

It's working fine.  Filter option & Userrelationship works fine separately. but when i put these 2 together then i am getting below error message

"There's not enough memory to complete this operation".. see below dax to get rolling 12 months sum

Der_Last12M_Failures =

var MinMonth = IF((MIN(FE_DDSCALLRATE[Der_Reg_YYYYMM])) = 0,BLANK(),LEFT(MIN(FE_DDSCALLRATE[Der_Reg_YYYYMM]),4)-1 & RIGHT(MIN(FE_DDSCALLRATE[Der_Reg_YYYYMM]),2))

RETURN

CALCULATE([Der_DistinctCntFailures],

FILTER(ALL(FE_DDSCALLRATE),

FE_DDSCALLRATE[Der_Reg_YYYYMM] >= value(MinMonth) &&

FE_DDSCALLRATE[Der_Reg_YYYYMM] < MAX(FE_DDSCALLRATE[Der_Reg_YYYYMM])),

USERELATIONSHIP(Month_Lookup[Der_Deliver_YYYYMM],FE_DDSCALLRATE[Der_Reg_YYYYMM])

)

[v-cherch-msft]

Hi @Peter_Jeyaraj_I

It seems you may try to optimize your data model. Please refer to this post.If it is not your case,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,

Cherie