Forum Discussion

bhatsuchi's avatar
bhatsuchi
Frequent Visitor
8 years ago
Solved

DAX to count rows with same value for Column A for a value in column B

Id	Email
1	[email protected]
2	[email protected]
3	[email protected]
1	[email protected]
1	[email protected]
2	[email protected]
2	[email protected]
3	[email protected]

Hello!

I have a table with data like above. I want to create a measure that can calculate total number of Ids with same value in the Email column.Should also ignore case for the email ids.  For the above example the total should be 2.(Ids 1 and 2)

I want another measure to calculate the total number of Ids with atleast 1 different email Id. For the above example the total should be 2.(Ids 2 and 3).

Please help! 

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    bhatsuchi,

     

    You may refer to the measures below.

    Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Id] ),
        CALCULATE ( COUNT ( Table1[Email] ) > DISTINCTCOUNT ( Table1[Email] ) )
    )
)

    Measure 2 =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Id] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Email] ) > 1 )
    )
)

16 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    bhatsuchi,

     

    You may refer to the measures below.

    Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Id] ),
        CALCULATE ( COUNT ( Table1[Email] ) > DISTINCTCOUNT ( Table1[Email] ) )
    )
)

    Measure 2 =
COUNTROWS (
    FILTER (
        VALUES ( Table1[Id] ),
        CALCULATE ( DISTINCTCOUNT ( Table1[Email] ) > 1 )
    )
)
  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    8 years ago

    Hi bhatsuchi,

    You can get the Count of Column B for each value in column A, by just setting the aggregation to Count in the visual where it is used

     

     

    Not sure why you need a separate DAX for that? Clarify if anything..

    • bhatsuchi's avatar
      bhatsuchi
      Frequent Visitor
      8 years ago

      Thanks Thejeswar. This is if I had just these 2 fields. I have more fields along with these in which case it does not count appropriately. Sorry for not specifying on the existence of other fields too.

      I need 2 measures though. Measure1- For all the Ids that appear more than once, I need a count of those that have the same email . Measure2 -For all the ids that appear more than once, a count of those that have atleast 1 different email.

      There are Ids that appear just once with 1 email id. That should not be considered in the calculation.

       

      Does this clarify?

      • Thejeswar's avatar
        Thejeswar
        Icon for Super User rankSuper User
        8 years ago

        Hi bhatsuchi,

        Is this what you are looking for?

         

         

        Measure = IF(COUNT(Table1[Email])>0 && COUNT(Table1[Email])<=1,count(Table1[Email]))
        Measure 2 = IF(COUNT(Table1[Email])>1,count(Table1[Email]))

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    v-chuncz-msft  , Thejeswar , Mariusz , Anonymous 

    I am new in powerbi and looking for solution that counts the repeated value in a column. Note: I ma using Direct query and not Import Mode.

     

    Repeated time         Count of Ids tha are repeating corresponds to (Repeated time) Column
    0 times         |      3 
1 times         |      2
2 times         |      2
3 times         |      1

    Data i have is like this:

     

     

    ID_ColumnID_001
    ID_001
ID_002
ID_002
ID_002
ID_003
ID_003
ID_003
ID_004
    ID_004
    ID_005
    ID_006
    ID_007
    ID_008
    ID_008
    ID_008
    ID_008

    Please see the screenshot for deatils of my problem:

     

    Thanks in advance,

    Kulchandra

     

     

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hi Anonymous 

       

      You can create a table like below.

      Table = 
ADDCOLUMNS(
    DISTINCT( DirectQueryTable[ID_Column] ), 
    "Repeated time", FORMAT( COUNTROWS( DirectQueryTable ), "" ) & " Times"
)
      Later create relationship on DirectQueryTable[ID_Column] = Table[ID_Column] and create measure COUNTROWS( DirectQueryTable )
       
      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      Mariusz Repczynski



       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Mariusz  i am not sure how we create table out of query