Forum Discussion

BI-Bola's avatar
BI-Bola
Frequent Visitor
6 years ago
Solved

Count Distinct where sum is

I am trying to get a distinct count of values where the sum of values is not zero   Below I have a basic distinct count:    Distinct Count = CALCULATE(DISTINCTCOUNT(AccountTransactions[Segment])...
Need Help
  • BI-Bola's avatar
    BI-Bola
    6 years ago

    Thanks for your suggestions all but strangely none worked with my dataset. I'm not sure if it's the size of the dataset or the fact that it is running through a direct query causing the issue.

     

    To get around it I created an additional table from the main query using a combination of the CALCULATETABLE, SUMMARIZE and FILTER functions and put a count card visual pulling data from new table.

BI-Bola's avatar
BI-Bola
Frequent Visitor
6 years ago

I have expanded the example table so that it is more in line with my dataset, apologies if it was unclear.

 

V-lianl-msft 

 

Thanks for the response but your formula does't seem to work for the updated table

 

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi BI-Bola ,

     

    After my test, I can get the expected results.

    If my understanding is wrong, please share the result you expect.

     

    Best Regards,
    Liang
    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

      Hi BI-Bola 

       

      Heve you tried the solution which i suggested.

       

      measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount])),If(Total>0,1,0))

       

      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.

    • Tan_Bui's avatar
      Tan_Bui
      New Member
      6 years ago

      Thanks a lot. I have tried this for my case with conditional distinct count and it worked 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        If this resolves your solution mark it as a solution so that it will help others and don't hesitate to give kudos.

         

        Thanks,

        Pravin