Forum Discussion

Arshadjehan's avatar
Arshadjehan
Icon for Helper I rankHelper I
5 years ago
Solved

DISTINCTCOUNT with one compulsory Condition

I have a table which contains Projects data for multiple years. A "key" calculated column is created concatenating "Year" and "Project_Code" Column. A project can be there for multiple years.   Req...
Need Help
  • danextian's avatar
    danextian
    5 years ago

    Please try this formula below which is similar to what I have previously posted.

    Count of Years for Project ID in 2020 =
    VAR _2020 =
    CALCULATE (
    COUNT ( 'Fact'[Year] ),
    FILTER ( ALLEXCEPT ( 'Fact', 'Fact'[Project ID] ), 'Fact'[Year] = 2020 )
    )
    RETURN
    COUNTX ( SUMMARIZE ( 'Fact', 'Fact'[Project ID], 'Fact'[Year], "value", _2020 ), [value] )


    You should be able to get this result return the numbers of years for S. IDs 1, 3 & 5.

     

Arshadjehan's avatar
Arshadjehan
Icon for Helper I rankHelper I
5 years ago

Thanks amitchandak  for the response.

But your suggested measure will filter only projects of 2020. I need a measure which could give me number of years a project is there is the list and is still in the list (last year of every project is 2020).