Forum Discussion
Helper IDISTINCTCOUNT with one compulsory Condition
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.
Super UserArshadjehan , You data format is not clear you can have measure like
calculate(coutrows(Table), filter(Table, Table[year]=2020))
Can use year or year without time intelligence
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Helper IThanks 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).
- danextian
Hi Arshadjehan ,
Try this:Count of Years for Project in 2020 = VAR _2020 = CALCULATE ( COUNT ( 'Fact'[Years] ), FILTER ( ALLEXCEPT ( 'Fact', 'Fact'[Project] ), 'Fact'[Years] = 2020 ) ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Fact'[Years] ), FILTER ( 'Fact', _2020 >= 1 ) )- Arshadjehan
danextian , Anonymous , amitchandak I think I am unable to explain my problem well.
I try again.
Here is a portion of my table:
Year Project ID Cost Key 2009 10042 1000 2009-010042 2014 130428 1000 2014-130428 2014 140168 1000 2014-140168 2015 150242 1000 2015-150242 2015 140176 1000 2015-140176 2015 140177 1000 2015-140177 2015 140179 1000 2015-140179 2015 140174 1000 2015-140174 2015 150249 1000 2015-150249 2016 140180 1000 2016-140180 2016 110086 1000 2016-110086 2016 110085 1000 2016-110085 2020 130426 1000 2016-130426 2016 140181 1000 2016-140181 2016 140178 1000 2016-140178 2016 140168 1000 2016-140168 2016 60282 1000 2016-060282 2016 90140 1000 2016-090140 2016 100157 1000 2016-100157 2016 100159 1000 2016-100159 And this (in Excel) is similar to what i want:
S No. Project ID Life of Project in Years Cost 1 170175 4 95000000 2017 1 5000000 2018 1 10000000 2019 1 30000000 2020 1 50000000 2 150890 4 184225000 2015 1 30000000 2016 1 50000000 2017 1 40000000 2018 1 64225000 3 170296 4 72991000 2017 1 10000000 2018 1 23391000 2019 1 35000000 2020 1 4600000 4 160564 4 94741000 2016 1 5000000 2017 1 21000000 2018 1 38741000 2019 1 30000000 5 170115 4 69817000 2017 1 20000000 2018 1 11834000 2019 1 19818000 2020 1 18165000 Only difference is that I want a measure who takes into consideration only those projects which are included for 2020 as well and exclude all those which were completed prior to 2020. In above example, only Projects at S No. 1, 3 and 5 are to be returned by measures.
Hope now it stands explained.
- danextian
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.