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 )
    )
)
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous 

 

Sorry, missed CALCULATE()

 

Table = 
ADDCOLUMNS(
    DISTINCT( DirectQuery[ID_ColumnID_001]), 
    "Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ), "" ) & " Times"
)

 

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 Great! I think one thing missing is. I am not seeing anything that is 

0 Times repeated = 

Is our dynamic table showing data like

ID_001 as  = 1 time repeated?

and

ID_001

ID_001

as =  2 times repeated?

 

After we got this. I would like to know if we can group repeated tiems like this example:

0 times repeated       |    50 ids 
1-3 times repeated     |    20 ids
4-5 times repeated     |    10 ids
>5 times repated       |     8 ids

Mariusz  Thank you so much for your help on this.

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

    Hi Anonymous 

     

     

    Please see the adjusted code.

    Table = 
SELECTCOLUMNS( 
    ADDCOLUMNS(
        DISTINCT( DirectQuery[ID_ColumnID_001] ), 
        "no", CALCULATE( COUNTROWS( DirectQuery ) ) -1
    ),
    "ID_ColumnID_001", [ID_ColumnID_001],
    "Repeated time no", [no],
    "Repeated time", 
        SWITCH(
            TRUE(),
            [no] = 0, "0",
            [no] IN{ 1, 2, 3 }, "1-3",
            [no] IN{ 4, 5 }, "4-5",
            "> 5"
        ) & " times reported"
)

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski