'DAX query with DISTINCT values, avoid repetition

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( 
     'Graphics Installed Base',
    "SerialNumber", 'Graphics Installed Base'[SerialNumber], -- 0
    "ProductNumber", 'Graphics Installed Base'[ProductNumber] -- 1
    )
)

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

 'Graphics Installed Base'[ProductLine] = "TW",
 'Graphics Installed Base'[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 (
        'Graphics Installed Base',
         "SerialNumber", 'Graphics Installed Base'[SerialNumber], -- 0
         "ProductNumber", 'Graphics Installed Base'[ProductNumber], -- 1
         "COIDate", 'Graphics Installed Base'[COIDate], -- 2
         "ProductFullName", 'Graphics Installed Base'[ProductFullName], -- 3
        ),
     'Graphics Installed Base'[ProductLine] = "TW",
     'Graphics Installed Base'[COIDate] <> BLANK (), format('Graphics Installed Base'[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( 
             'Graphics Installed Base',
            "SerialNumber", 'Graphics Installed Base'[SerialNumber], -- 0
            "ProductNumber", 'Graphics Installed Base'[ProductNumber] -- 1
            )
    )
    , 'Graphics Installed Base'[ProductLine] = "TW",
        'Graphics Installed Base'[COIDate] <> BLANK (),
        format('Graphics Installed Base'[COIDate],"YYYY/MM/DD") <= "2018/12/12")

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

Solution 1:^[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'DAX query with DISTINCT values, avoid repetition

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]