Forum Discussion

CA8172's avatar
CA8172
Icon for Helper I rankHelper I
6 years ago

Need a Count on Distinct Information

I am trying to create a Measure or a Column that will count the distinct information either based on an account number or first/last name.  I tried COUNTROWS, I tired DISTINCT, and even 'Grouped By',...
Need Help
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago


Hi CA8172 , try this:

Measure = CALCULATE(COUNTROWS(myTable),ALLEXCEPT(myTable,myTable[Account Number]))

 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

  • CA8172's avatar
    CA8172
    Icon for Helper I rankHelper I
    6 years ago

    Nathaniel_C  - I did as you suggested (see below), but it counted each row and is showing 21,912 for each Case Number, etc (see screen shot) when I try to put it all in a Table visualization.

     

    PRODUCTS = CALCULATE(COUNTROWS(TableA),ALLEXCEPT(TableA,TableA[Account Number]))
     
    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi CA8172 ,
      Would you share a picture of your data model relationships?

      Thanks,

      Nathaniel

      • CA8172's avatar
        CA8172
        Icon for Helper I rankHelper I
        6 years ago

        Nathaniel_C- it is only one table so there is no relationships at this point

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      CA8172 

       

      It is filtered with account number, if you drag account number column to the table it should be clear.
      If you want to countrows for each Case Number(Request Id) you need to change the "ALLEXCEPT" function to ALLEXCEPT(TableA,TableA[Case Number])).

      Paul