Help

[SantiagoTorres]

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

[v-stephen-msft]

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]

Hi Stephen, thank you for your response,

Still not getting the values I need, 

Project Number	Project Address	Date Created	Assignee(S)
B037 B038	20 Miller Street	25/8/21	Steve Gray
B021	3 Market Street	25/8/21	Jake Van der Zant
B080	331 Orrong Road	25/8/21	Paul O'Callaghan
B080	331 Orrong Road	25/8/21	Paul O'Callaghan
B035 B036	12-18 Miller Street	25/8/21	Steve Gray
B021	3 Market Street	25/8/21	Jake Van der Zant
B010	10 Tennyson Street	25/8/21	Paul O'Callaghan
B010	10 Tennyson Street	25/8/21	Paul O'Callaghan
B035 B036	12-18 Miller Street	25/8/21	Steve Gray
B080	331 Orrong Road	23/8/21	Paul 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

[Ashish_Mathur]

Hi,

In the Table that you have shared in your post, could you kinldy show the expected result.

[SantiagoTorres]

Hi Ashish, thanks for your response,

As you can see below, I'm getting the values I need per Assignee (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).

I cannot load all the data here (I couldn't find a way to load attachments), but please see below the logic of my calculation with a portion of the data I have.

Project Number	Project Address	Date Created	Assignee(S)
B037 B038	20 Miller Street	25/8/21	Steve Gray
B021	3 Market Street	25/8/21	Jake Van der Zant
B080	331 Orrong Road	25/8/21	Paul O'Callaghan
B080	331 Orrong Road	25/8/21	Paul O'Callaghan
B035 B036	12-18 Miller Street	25/8/21	Steve Gray
B021	3 Market Street	25/8/21	Jake Van der Zant
B010	10 Tennyson Street	25/8/21	Paul O'Callaghan
B010	10 Tennyson Street	25/8/21	Paul O'Callaghan
B035 B036	12-18 Miller Street	25/8/21	Steve Gray
B080	331 Orrong Road	23/8/21	Paul O'Callaghan

I need to get project numbers by the date the site has been visited (#visits), but when the project number is repeated on the same date, I should count it just once. For instance, in this  little table, we should get as a result that B080=2 and B010=1, etc.,

Am I clear with that information?, kind of hard to show more details, sorry.

Thanks,

Andres Torres

[Ashish_Mathur]

Hi,

Has Stephen Tao alsready answered your question?  Do you still need help?

[SantiagoTorres]

Hello. thanks for your response,

It's working for me with the following modification:

Site Visits =

COUNTROWS(

SUMMARIZE('4. CoW Productivity', '4. CoW Productivity'[Project Number], '4. CoW Productivity'[Date Created],'4. CoW Productivity'[Assignee(S)])

)

Can you see I got the same values now, but in the total, I got 261 instead of 253, which is correct.

Many thanks,

ASTM

[v-stephen-msft · Accepted solution]

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.

[Fowmy]

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

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