cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrisPollock Regular Visitor
Regular Visitor

Count Duplicates Within a Range of Values

Hello,

Looking for a solution to my issue currently I am trying to have a duplicate count to display when a room is occupied within a duration of time 

Example I want rooms that are the same that have the same room on the same day within the same time range to be counted as a duplicate not just when its start and end time are the same.

Power BI Question.png

Thanks in advance for any help n this subject

7 REPLIES 7
ChrisPollock Regular Visitor
Regular Visitor

Re: Count Duplicates Within a Range of Values

bump.

Super User
Super User

Re: Count Duplicates Within a Range of Values

@ChrisPollock

 

Try this calculated column

 

Duplicate Test =
VAR CurrentStart = TableName[Date/Start Time Required]
VAR CurrentEnd = TableName[Date/End Time Required]
RETURN
    CALCULATE (
        COUNTROWS ( TableName ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Room (Option 1)], TableName[Date Rqeuired] ),
            OR (
                TableName[Date/Start Time Required] > CurrentStart
                    && TableName[Date/Start Time Required] < CurrentEnd,
                TableName[Date/End Time Required] > CurrentStart
                    && TableName[Date/End Time Required] < CurrentEnd
            )
        )
    )
ChrisPollock Regular Visitor
Regular Visitor

Re: Count Duplicates Within a Range of Values

@Zubair_Muhammad
I'm not the most familiar with DAX but that returned this warning
A single value for column 'Date/Start-Time Required' in table 'Overlapping' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Super User
Super User

Re: Count Duplicates Within a Range of Values

@ChrisPollock

 

You tried adding it as a MEASURE....

 

The formula I wrote was for a calculated colum

There is an option to add Column as well...Please use that OPTION

ChrisPollock Regular Visitor
Regular Visitor

Re: Count Duplicates Within a Range of Values

@Zubair_Muhammad

Thank you for the help with this, I am very new to power BI, but seem to still have an issue with this calculable column it is complaining about a 'Token Eof Expected' on VAR CurrentStart
My code currently looks like this:

Duplicate Test 
=
VAR CurrentStart = Overlapping[#"Date/Start-Time Required"]
VAR CurrentEnd = Overlapping[#"Date/End-Time Required"]
RETURN
    CALCULATE (
        COUNTROWS ( Overlapping),
        FILTER (
            ALLEXCEPT ( Overlapping, Overlapping[#"Room (Option 1)"], Overlapping[Date Required] ),
            OR (
                Overlapping[#"Date/Start-Time Required"] > CurrentStart
                    && Overlapping[#"Date/Start-Time Required"] < CurrentEnd,
                Overlapping[#"Date/End-Time Required"] > CurrentStart
                    && Overlapping[#"Date/End-Time Required"] < CurrentEnd
            )
        )
    )




Super User
Super User

Re: Count Duplicates Within a Range of Values

Hi Chris

Please share your file
I will look into it after few hours and get back to you
ChrisPollock Regular Visitor
Regular Visitor

Re: Count Duplicates Within a Range of Values

I can't share files in post but I have dropped it here...
https://www.dropbox.com/home?preview=Overlapping.pbix

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 57 members 2,562 guests
Please welcome our newest community members: