Forum Discussion
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.
Requirement: I want to calculate count of years a project is there in the list of projects. Only condition is that I want only those projects which are still there in the list i.e. for the year 2020 as well.
For example two projects are there for five years each:
Project1: 2013, 2014, 2016, 2017, 2018
Project2: 2017, 2017, 2018, 2019, 2020
I need a DAX measure which can exclude Project1 as it was retired in 2018, but include Project2 in DISTINCTCOUNT as it is still included in 2020.
Any help would be appreciated.
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.
8 Replies
Arshadjehan , 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_0uAThanks 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).
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 ) )
Hi Arshadjehan ,
Here are the steps you can follow：
1. Create measure.
2020 = SWITCH( TRUE(), 2020 in SELECTCOLUMNS('Table',"Project1",'Table'[Project1]),"Project1", 2020 in SELECTCOLUMNS('Table',"Project2",'Table'[Project2]),"Project2", BLANK())2. Put it into card visual
3. Result：
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
