Matching a value in a range of values

[A_S]

Hello, 

I am working on a system to check data on certificates that have been sent to customers to issue to students. My issue is that the certificates are sold in books of 25 and only the min and max number of each book is recorded in the sales records. 

I am looking to create a column which pulls the Customer Reference Number for an individual certificate by examining the two columns (min value and max value) and determining when it is in between those values. 

I'm not sure if I have explained this very well but I have attached a github link below to my dummy pbi file and dummy data. 

https://github.com/A-S-1/Dummy-Data-and-PBI/find/main

Please let me know if you need more information and thanks for reading. 

[A_S]

Brilliant, thank you for your help.

[DataInsights · Accepted solution]

@A_S,

Try this calculated column in the Certificates table :

Customer Reference Number = 
VAR vCertNum = Certificates[Certificate number]
VAR vTable =
    FILTER (
        Orders,
        vCertNum >= Orders[Min Cert number]
            && vCertNum <= Orders[Max Cert number]
    )
VAR vResult =
    MAXX ( vTable, Orders[Customer Reference] )
RETURN
    vResult