DAX query with DISTINCT values, avoid repetition

Question

I am doing a DAX query able to retrieve a list of products installed before a certain date (COIDate) , and that have a specific ProductLine. But, it returns the same product multiple times.

I would like to know if there is a way, I'm sure there is, to write this same query, but specifying that, if a product has the same ProductNumber and SerialNumber,it's the same product and must not be repeated in the results, so it needs a sort of group by(SerialNumber,ProductNumber) or something like DISTINCT. My background is SQL language, so the syntax needed here is different. I tried just a distinct, like this:

EVALUATE 
    DISTINCT(
    SELECTCOLUMNS( 
     'Analysis Services DB',
    "SerialNumber", 'Analysis Services DB'[SerialNumber], -- 0
    "ProductNumber", 'Analysis Services DB'[ProductNumber] -- 1
    )
)  

And it works. Now I need to add also these filters:

 'Analysis Services DB'[ProductLine] = "TW",
 'Analysis Services DB'[COIDate] <> BLANK (), format('Graphics Installed Base'[COIDate],"YYYY/MM/DD") <= "2018/12/12"

This is the query that I have, that I am trying to modify accordingly to what I've said above:

EVALUATE
    CALCULATETABLE ( SELECTCOLUMNS (
        'Analysis Services DB',
         "SerialNumber", 'Analysis Services DB'[SerialNumber], -- 0
         "ProductNumber", 'Analysis Services DB'[ProductNumber], -- 1
         "COIDate", 'Analysis Services DB'[COIDate], -- 2
         "ProductFullName", 'Analysis Services DB'[ProductFullName], -- 3
        ),
     'Analysis Services DB'[ProductLine] = "TW",
     'Analysis Services DB'[COIDate] <> BLANK (), format('Analysis Services DB'[COIDate],"YYYY/MM/DD") <= "2018/12/12"
 

EDIT: What I've added: a query with a DISTINCT and a filter, but it's missed al the other fields. Clearly there will be another way.

EVALUATE
        CALCULATETABLE( 
        DISTINCT( 
            SELECTCOLUMNS( 
             'Analysis Services DB',
            "SerialNumber", 'Analysis Services DB'[SerialNumber], -- 0
            "ProductNumber", 'Analysis Services DB'[ProductNumber] -- 1
            )
    )
    , 'Analysis Services DB'[ProductLine] = "TW",
        'Analysis Services DB'[COIDate] <> BLANK (),
        format('Analysis Services DB'[COIDate],"YYYY/MM/DD") <= "2018/12/12")

Help me on adding the other fields, in the Select ! Thank you !

Answer 1

try this:

EVALUATE
DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            'Graphics Installed Base','Graphics Installed Base'[COIDate] <= DATE(2018,12,12) 
            &&
            'Graphics Installed Base'[ProductNumber] = "TW"),
        "SerialNumber", 'Graphics Installed Base'[SerialNumber],
        "ProductNumber", 'Graphics Installed Base'[ProductNumber]
    )
)

putting the FILTER inside the SELECTCOLUMNS.

Answer 2

i dont know your table but you'd need to think more simpler...

Modelling ---> New Table

Filtered Table =
CALCULATETABLE (
    'Analysis Services DB',
    'Analysis Services DB'[ProductLine] = "TW",
    'Analysis Services DB'[COIDate] <> BLANK (),
    'Analysis Services DB'[COIDate] <= DATE ( 2023, 12, 12 )
)

you need to have one or more conditions/rules to distinct the dBase... like latest date or any other rule on the other fields...

Filtered Table (Latest Date) =
FILTER (
    CALCULATETABLE (
        'Analysis Services DB',
        'Analysis Services DB'[ProductLine] = "TW",
        'Analysis Services DB'[COIDate] <> BLANK (),
        'Analysis Services DB'[COIDate] <= DATE ( 2023, 12, 12 )
    ),
    CALCULATE (
        MAX ( 'Analysis Services DB'[COIDate] ),
        ALLEXCEPT ( 'Analysis Services DB', 'Analysis Services DB'[ProductNumber] )
    ) = 'Analysis Services DB'[COIDate]
)