Seeking help with the DAX formula : Groupby and lookupvalue functions

[Nicks612]

Dear All,

I need help with the following issues:

SerialNumber	OK / NOK	DBKey	Problem Description	Remarks
23032406494	NOK	20230405082149	I have these three columns in my data. I want to return these three columns concerning the minimum value of DBKey. (Refer To Table.)	DBKey is a combination of date and test time in the below format - YYYY-MM-DD-HH-MM-SS, which is always unique.
23032406494	NOK	20230401082331
23032506584	NOK	20230331082009
23032006260	OK	20230330081531
23032106349	OK	20230329064408
23032106349	OK	20230329063959
23032506584	OK	20230325190045
23032406494	NOK	20230324185510
23032006260	NOK	20230321155209
23032006260	OK	20230321094402

I want to generate the following table which contains a unique serial number with a minimum value of their respective DBKey values. For Example, for the "23032406494" SerialNumber, there are three different entries of DBKey which are - "20230405082149", "20230401082331" and "20230324185510". Out of these DBKey values for the mentioned SerialNumber, this DBKey value "20230324185510" should come as this is the lowest value among the three DBKey values. 

Resulted Columns:
SerialNumber	OK / NOK	DBKey
23032006260	OK	20230321094402
23032106349	OK	20230329063959
23032406494	NOK	20230324185510
23032506584	NOK	20230325190045

I have implemented this logic using the GroupBy and merge option in the Power BI query editor. However, I need to do it using DAX formulas like table function and table manipulation functions - after that, I create a measure to count the number of serial numbers.

Kindly, provide the solutions and let me know if anyone needs further clarifications or doubts on the problem.

Regards,

Yash P

[Dangar332 · Accepted solution]

Hi, @Nicks612 

Try below measure

measure(OK) = 
 var a =ADDCOLUMNS(
    VALUES('Table'[SerialNumber]),
    "DBKey",
    MINX(
                 FILTER(ALL('Table'),
                       'Table'[SerialNumber]= EARLIER('Table'[SerialNumber])
                    ),
                 'Table'[DBKey]
            ),
    "OK/NOK",
    MINX(
        FILTER(
            ALL('Table'),
            'Table'[DBKey]=MINX(
                             FILTER(
                                ALL('Table'),
                                'Table'[SerialNumber]= EARLIEST('Table'[SerialNumber])
                            ),'Table'[DBKey])
        ),
        'Table'[OK / NOK])
)
RETURN
COUNTX(FILTER(a,[OK/NOK]="OK"),[OK/NOK])

For NOK measure Replace OK with "NOK" in 

COUNTX(FILTER(a,[OK/NOK]="NOK"),[OK/NOK]) Part

Best Regards
Dangar 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntrikshSharma]

@Nicks612 Why do you have NOK for: 

23032506584

NOK

20230325190045

Solution 1 = 
ADDCOLUMNS (
    ADDCOLUMNS ( 
        VALUES ( 'Table'[SerialNumber] ),
        "DBKey", CALCULATE ( MIN ( 'Table'[DBKey] ) )
    ),
    "OK/NOK", 
        CALCULATE ( 
            SELECTEDVALUE ( 'Table'[OK / NOK] ),
            TREATAS ( { [DBKey] }, 'Table'[DBKey] )
        )
)

Solution 2 = 
CALCULATETABLE (
    'Table',
    TREATAS ( 
        ADDCOLUMNS ( 
            VALUES ( 'Table'[SerialNumber] ),
            "DBKey", CALCULATE ( MIN ( 'Table'[DBKey] ) )
        ),
        'Solution 1'[SerialNumber],
        'Table'[DBKey]
    )
)

[Nicks612]

Thanks, @AntrikshSharma, for the prompt reply!!

Sorry, for the typo mistake. It is "OK" instead of "NOK". 

[Dangar332]

Hi, @Nicks612 

Try below formula for new table

New Table = 
 ADDCOLUMNS(
    VALUES('Table'[SerialNumber]),
    "DBKey",
    MINX(
                 FILTER(ALL('Table'),
                       'Table'[SerialNumber]= EARLIER('Table'[SerialNumber])
                    ),
                 'Table'[DBKey]
            ),
    "OK/NOK",
    MINX(
        FILTER(
            ALL('Table'),
            'Table'[DBKey]=MINX(
                             FILTER(
                                ALL('Table'),
                                'Table'[SerialNumber]= EARLIEST('Table'[SerialNumber])
                            ),'Table'[DBKey])
        ),
        'Table'[OK / NOK])
)

Best Regards
Dangar 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nicks612]

Thanks, @Dangar332, for the prompt reply. I have tried this formula and it could be helpful. However, I have a query related to the same concerns that,

I want to count rows of "OK" and "NOK" serial numbers in the generated table using the measure as per your solution. For Example, one measure should contain a value of 3 for "OK" and 1 for "NOK". Along with your query, How do count the serial numbers from the virtual table?

[Dangar332]

Hi, @Nicks612 

Try below measure

New Table = 
 ADDCOLUMNS(
    VALUES('Table'[SerialNumber]),
    "DBKey",
    MINX(
                 FILTER(ALL('Table'),
                       'Table'[SerialNumber]= EARLIER('Table'[SerialNumber])
                    ),
                 'Table'[DBKey]
            ),
    "OK/NOK",
    MINX(
        FILTER(
            ALL('Table'),
            'Table'[DBKey]=MINX(
                             FILTER(
                                ALL('Table'),
                                'Table'[SerialNumber]= EARLIEST('Table'[SerialNumber])
                            ),'Table'[DBKey])
        ),
        'Table'[OK / NOK])
)

Best Regards
Dangar 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nicks612]

Thanks! @Dangar332, It is working correctly, but in my case, I have a time slicer from another table and if I create one more physical table then it is difficult to establish the relationship between the two tables.

Earlier, I was unaware of this scenario, therefore I didn't mention that. Sorry, for the inconvenience happened.

Can you please guide me to make a logic that gives me a total serial number of the "OK" and "NOK" values (two different measures for "OK"  and "NOK") by creating a virtual table in measure without creating a physical table? 

[Dangar332 · Accepted solution]

Hi, @Nicks612 

Try below measure

measure(OK) = 
 var a =ADDCOLUMNS(
    VALUES('Table'[SerialNumber]),
    "DBKey",
    MINX(
                 FILTER(ALL('Table'),
                       'Table'[SerialNumber]= EARLIER('Table'[SerialNumber])
                    ),
                 'Table'[DBKey]
            ),
    "OK/NOK",
    MINX(
        FILTER(
            ALL('Table'),
            'Table'[DBKey]=MINX(
                             FILTER(
                                ALL('Table'),
                                'Table'[SerialNumber]= EARLIEST('Table'[SerialNumber])
                            ),'Table'[DBKey])
        ),
        'Table'[OK / NOK])
)
RETURN
COUNTX(FILTER(a,[OK/NOK]="OK"),[OK/NOK])

For NOK measure Replace OK with "NOK" in 

COUNTX(FILTER(a,[OK/NOK]="NOK"),[OK/NOK]) Part

Best Regards
Dangar 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nicks612 · Accepted solution]

@Dangar332, Thank you so much for the solution! The solution which you provided is working.