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 )
    )
)
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Mariusz Thank you for the quick reply. Table is created and relation also build between two columns. Between new table [id_column] and direct query table [id_column]. As per your instruction I have created another mesure also that counts rows for direct query table. But i didn't understand how to achive the result i wanted like i posted in my question.

Here is the result I am getting after used values from dynamic table created.
please find the sample PowerBi file (GoogleDrive). I have created this powerbi file using exact sample data i have provided in my quesion and applied your solution on that. If you can work on that file and send me back. That would be really helpful.  

 

 

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

 

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

    Hi Anonymous 

     

    and - 1 to start @ 0 Times.

     

    Table = 
ADDCOLUMNS(
    DISTINCT( DirectQuery[ID_ColumnID_001] ), 
    "Repeated time", FORMAT( CALCULATE( COUNTROWS( DirectQuery ) ) -1, "" )  & " Times"
)
  • 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