Return a unique value by counting specific values under multiple criterias

[mariuscourcel]

Hello all,

I am trying to return a unique number (this is important, because i know how to get that number through a table visual, but not as a single value) based on specific criterias : i need the number of agencies that have placed multiple orders in a day. 

Something like "distinctcount agency if number of orders in one day >1"

I have the following table :

DATEVE = Date

NUMEVE = Order ID

Cle_Liv = Agency ID

The idea would be to do a Group By, but i will need to the same type of calculations in this table with different criterias, and i do not wish to add "who knows" amount of new tables everytime.

I would also like to keep as much details as possible (like the order ID for example), so I am avoiding the "Group By".

Instead i tried to get a calculated column that would return "Yes" if the line is considered "Multiple" or "No" if not.

Something like this :

I could then create simple measure to deduce what i need.

I was thinking, maybe i could count the duplicate values on dates or agency, but i didn't manage to do both within a single calculation. I tried the following :

Test = IF(

COUNTROWS(

FILTER(Bd_03_Franco;Bd_03_Franco[Cle_Liv]=EARLIER(Bd_03_Franco[Cle_Liv])))>1;1;BLANK()

)

I am a bit stuck on this one.

I am open to any solution. If i was not clear in my question, please do say so 😅

The answer might be obvious for some of you, so your wisdom would be appreciated for a novice !

[v-eachen-msft · Accepted solution]

Hi @mariuscourcel ,

Do you mean to get the "Test" formula? Try to add "Date" condition.

Test =
IF (
    COUNTROWS (
        FILTER (
            Bd_03_Franco;
            Bd_03_Franco[Cle_Liv] = EARLIER ( Bd_03_Franco[Cle_Liv] )
                && Bd_03_Franco[Date] = EARLIER ( Bd_03_Franco[Date] )
        )
    ) > 1;
    1;
    BLANK ()
)

[mariuscourcel]

Hi @v-eachen-msft 

Yes, that's the idea.

It does work for my problem, although this might not be the best way to do it. If you have any other way, just has a way of learning more, please do share.

I will accept this as answer.

Thank you !