Summarise DAX

[swongbg]

Hi All

I would like to count the number of unique projects I have with revenue and was wondering if I should use the summarise function?

I was considering using DistinctCount but I believe it's done on a row-by-row basis which should give an answer of 3 but project A shouldn't be counted anymore as the revenue cancels out to 0 after march.

Thanks!

[v-zhenbw-msft]

Hi @swongbg ,

We can create a calculate column and a measure to meet your requirement.

1. First you need to have a date column like this,

2. Then we can create a secondary column to calculate the accumulate of revenue.

Column = CALCULATE(SUM('Table'[Revenue]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=EARLIER('Table'[Date])&& 'Table'[ProjectID]=EARLIER('Table'[ProjectID])))

3. At last we can create a measure to calculate the distinct count of Project ID.

Count ID = CALCULATE(DISTINCTCOUNT('Table'[ProjectID]),FILTER('Table','Table'[Column]>0))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[swongbg]

many thanks @v-zhenbw-msft 

For YTD march, the count should be 2 - project B have revenue of $35 and project C have revenue of $50 (just no transaction in March)

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@swongbg , Try like

countx(filter(summarize(Table,Table[project],"_1",Sum(Table[revenue])),[_1]>0),[project])

[swongbg]

Can I use this formula directly? It says the expression is not valid or appears to be incomplete...