Forum Discussion

CA8172's avatar
CA8172
Helper 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
Community 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
Helper 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
    Community Champion
    6 years ago

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

    Thanks,

    Nathaniel

    • CA8172's avatar
      CA8172
      Helper I
      6 years ago

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

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Dummied PBIX 
        Hi CA8172 ,
        So here is a pbix with dummied data, it is one table with a measure that is applied to a table. See if this gives you an idea.

        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

  • 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