Forum Discussion

Mitig's avatar
Mitig
Frequent Visitor
6 years ago
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....
  • az38's avatar
    az38
    6 years ago

    Mitig 

    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)))
  • Anonymous's avatar
    Anonymous
    6 years ago
    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
az38's avatar
az38
Community Champion
6 years ago

Mitig 

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)))
Anonymous's avatar
Anonymous
Not applicable
6 years ago
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