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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sovereignauto
Helper III
Helper III

Finding Values if they historically exist

Good Morning, 

 

Not sure how to phrase this so will give it my best shot.

 

Below is a table that shows this vehicle was on hire with lic number 2888 4 times but there was some other lic numbers in the middle. 

 

what im looking to do is somehow identify that thsi has happend and flag the rows in which this happend. 

 

the key here is there must be a "split" in the licence to flag it. 

 

by "flag" this could just be a column with "yes" or "1" or to be honest anything in it. 

 

 

Actual Collection DateActual Delivery DateLicensing AuthorityLicenceStart DateEnd DateLicence Type (Licence) (Licence)

 04/01/2024LIC-00470518/09/202317/09/2024Annual
29/12/202330/10/2023LIC-00399305/06/202304/06/2024Annual
27/10/202314/09/2023LIC-00288813/12/202213/12/2023Annual
08/09/202301/08/2023LIC-00288813/12/202213/12/2023Annual
10/07/202305/06/2023LIC-00399305/06/202304/06/2024Annual
30/05/202320/03/2023LIC-00352222/03/202318/05/2023Annual
10/03/202317/02/2023LIC-00328927/02/202326/02/2024Annual
23/01/202311/01/2023LIC-00301011/01/202311/03/2023Transfer
30/01/202325/01/2023LIC-00288813/12/202213/12/2023Annual
20/12/202214/12/2022LIC-00288813/12/202213/12/2023Annual
1 ACCEPTED SOLUTION
sovereignauto
Helper III
Helper III

i Got it working in excel in the end.

I found the last time the licence was used prior to the current and then coutn how many (if any) ither records there were in between, if none then didnt flag it if there was then i flagged it. 

View solution in original post

3 REPLIES 3
sovereignauto
Helper III
Helper III

i Got it working in excel in the end.

I found the last time the licence was used prior to the current and then coutn how many (if any) ither records there were in between, if none then didnt flag it if there was then i flagged it. 

v-yaningy-msft
Community Support
Community Support

Hi, @sovereignauto 

May I ask if this is the expected output you are looking for? Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

vyaningymsft_0-1704850820793.png

 

Measures:

Tag =

VAR _Counts =

    CALCULATE (

        COUNTROWS (

            FILTER (

                'Table',

                'Table'[Licensing Authority] = SELECTEDVALUE ( 'Table'[Licensing Authority] )

            )

        ),

        ALLEXCEPT ( 'Table', 'Table'[Licensing Authority] )

    )

RETURN

    IF ( _Counts > 1, "yes" )

 

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

vicky_
Super User
Super User

Has Prev = INT(CALCULATE(
    COUNTROWS('Table (2)'), 
    FILTER(ALL('Table (2)'), 
        'Table (2)'[Licensing Authority] = EARLIER('Table (2)'[Licensing Authority]) && 
        'Table (2)'[Actual Delivery Date] < EARLIER('Table (2)'[Actual Delivery Date])
    )
) > 0)

Hey the above is what i've come up with - the basic idea is to check if the same license has an "actual delivery date" earlier than the current row's, and then return 1 if that's true.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.