Forum Discussion

SantiagoTorres's avatar
SantiagoTorres
Helper I
4 years ago
Solved

Help

Hello all,

 

Can anyone please help me to understand whats the mistake I'm making in the following DAX formula:

 

Site Visits = COUNTROWS(
SUMMARIZE('4. CoW Productivity', '4. CoW Productivity'[Project Number], '4. CoW Productivity'[Date Created]))
 
As you can see below, I'm getting the values I need (I already checked they're correct), but I don't understand why the total shows me a wrong value (it should be 261 instead of 253).
 

Many thanks,

 

Andres Torres

 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SantiagoTorres ,

     

    Sorry for my late reply.

     

    Try this

    Site Visits = COUNTROWS(DISTINCT('4  CoW Productivity'))

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    SantiagoTorres 

    Can you apply the following modified meaure?

    Site Visits =
SUMX (
    VALUES ( '4. CoW Productivity'[Assignee(S)] ),
    COUNTROWS (
        SUMMARIZE (
            '4. CoW Productivity',
            '4. CoW Productivity'[Project Number],
            '4. CoW Productivity'[Date Created]
        )
    )
)
    • SantiagoTorres's avatar
      SantiagoTorres
      Helper I
      4 years ago

      Hi, thank you so much for your response,

       

      I already applied this formula:

       
      Site Visits =
      SUMX(
      VALUES('4. CoW Productivity'[Assignee(S)]),
      COUNTROWS(
      SUMMARIZE(
      '4. CoW Productivity',
      '4. CoW Productivity'[Project Number],
      '4. CoW Productivity'[Date Created]
      )
      )
      )
       
      And I get these results,

      What you think??

       

      Many thanks,

       

      Andres Torres

       
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SantiagoTorres ,

     

    Try this measure

     

    Site Visits = CALCULATE(COUNTROWS('4  CoW Productivity'),ALLEXCEPT('4  CoW Productivity','4  CoW Productivity'[Assigness],'4  CoW Productivity'[Date Created],'4  CoW Productivity'[Project Number]))

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • SantiagoTorres's avatar
      SantiagoTorres
      Helper I
      4 years ago

      Hi Stephen, thank you for your response,

       

      Still not getting the values I need, 

       

      Project NumberProject AddressDate CreatedAssignee(S)
      B037 B03820 Miller Street25/8/21Steve Gray
      B0213 Market Street25/8/21Jake Van der Zant
      B080331 Orrong Road25/8/21Paul O'Callaghan
      B080331 Orrong Road25/8/21Paul O'Callaghan
      B035 B03612-18 Miller Street25/8/21Steve Gray
      B0213 Market Street25/8/21Jake Van der Zant
      B01010 Tennyson Street25/8/21Paul O'Callaghan
      B01010 Tennyson Street25/8/21Paul O'Callaghan
      B035 B03612-18 Miller Street25/8/21Steve Gray
      B080331 Orrong Road23/8/21Paul O'Callaghan

       

      I need to get project numbers by date (#visits), but when the project number is repeated on the same date, I should count it just once. For instance, in this table B080=2 and B010=1, etc.,

       

      Many thanks,

       

      Andres Torres

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi SantiagoTorres ,

         

        Sorry for my late reply.

         

        Try this

        Site Visits = COUNTROWS(DISTINCT('4  CoW Productivity'))

         

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.