Forum Discussion

Arshadjehan's avatar
Arshadjehan
Helper 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.

     

danextian's avatar
danextian
Super User
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
Helper I
5 years ago

danextian Thanks for the responce.

One thing more. Is there any way I can list only projects 1,3 and 5 and exclude all other not included in 2020?

 

  • danextian's avatar
    danextian
    Super User
    5 years ago

    If you remove all the other measures, [Count of Years for Project ID in 2020] will return blank for projects 2 and 5 and by default will not be shown in the matrix. Alternatively, you can filter by measure in the visual filter pane where [Count of Years for Project ID in 2020] is not blank.