Forum Discussion
Solved
DISTINCTCOUNT for Year Ranges with Two Columns
Greetings, Brand new to PBI here. I am trying to create a measure that counts "ID" column based off of "YEAR" column. HOWEVER, another column ("TERM") has different codes before 2016 than after....
Try this
Measure=
Var a=Calculate(distinctcount(table[id]),filter(table,table[year]<2016))
Var b=Calculate(distinctcount(table[id]),filter(table,table[year]>=2016 && table[code]<> 5 &&table[code]<> 10))
Return
If(max(year)<2015,a,b)
Thanks
Pravin
Thank you - I did look into this one after your reply, but due to there being duplicated rows, counting the raw number of rows would yield an excessive number of IDs. (I did not mention there were duplicates in the original posting, so that is definiely my bad for lack of clarity.) As such, it has to be a distinct count of IDs. I also edited the original post with a sample dataset and how the count would come out. Cheers!
maybe you should try a measure like
Measure = CALCULATE(DISTINCTCOUNT(Table1[ID]),
FILTER(ALL(Table1), Table1[YEAR]=SELECTEDVALUE(Table1[YEAR]) && (Table1[TERM]>10 || Table1[YEAR]<2016)))- Try this
Measure=
Var a=Calculate(distinctcount(table[id]),filter(table,table[year]<2016))
Var b=Calculate(distinctcount(table[id]),filter(table,table[year]>=2016 && table[code]<> 5 &&table[code]<> 10))
Return
If(max(year)<2015,a,b)
Thanks
Pravin