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 

 

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

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

    Hi Anonymous 

     

    Plesae see the below.

     

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • 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