Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summarise DAX

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!

 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try like

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!