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...
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 )
    )
)
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]))

       

      • bhatsuchi's avatar
        bhatsuchi
        Frequent Visitor
        8 years ago

        Measure1 should have the value 2 (For Id=1 and Id=2)

        Measure2 should have the value 2 ( For Id=2 and Id=3)

         

        If I were to write a SQL to calculate Measure2 it would be -

         

        SELECT count(distinct a.Id) FROM table1 a

        JOIN table1 b on b.Id = a.Id AND (LOWER(b.[email] )<>LOWER( a.[email]))