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,

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