Forum Discussion
DISTINCTCOUNT 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.
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 ) )
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.
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 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?
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.