Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mariuscourcel
Frequent Visitor

Return a unique value by counting specific values under multiple criterias

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 :

Capture.PNG

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 :

Capture00.PNG

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 !

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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 ()
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

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 ()
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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 !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors