DAX Functions (Related, Lookupvalue, if then, Unique, Max)

[Anonymous]

Hello, I am trying to create a complicated Power BI Report and I am having trouble figuring out  how exactly I can report what I need.

I have 2 different tables, one reports serial numbers, the error codes associated with it, and the date and time. Some serial numbers can have multiple failures but reports all the failures at 1 time (aka 1 serial on multiple rows with the same date and time).

The other table has all the errors codes that the serial number failed for and has a numerical value associated with that error code to determine the latest failure.

Date/Times do not match the serials for each table

Table 1:

Serial |          Date/Time           | Error Code

123      9/13/2022 1:00 PM           136

123      9/13/2022 1:00 PM           86

123      9/13/2022 1:00 PM           325

123      9/13/2022 1:00 PM           35

Table 2:

Serial |          Date/Time           | Error Code | Relative Failure Time

123      9/13/2022 1:26 PM          136                30

123      9/13/2022 1:31 PM           86                 24

123      9/13/2022 1:31 PM           325               154

123      9/13/2022 1:26 PM           35                 91

So I need to:

1. Find out if the serial number in Table 1 is unique

2. If not, look for that serial in Table 2, and find the last date and then the max value in Column Relative Failure Time

3. Take the row number of the max value in Column Relative Failure Time and give me the error code from that row (Column Error code)

4. Put that error code in Table 1 as Column Actual Error Code.

[johnt75 · Accepted solution]

I think the below should work

Actual error code =
VAR numEntries =
    CALCULATE (
        COUNTROWS ( 'Table 1' ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[Serial] )
    )
RETURN
    IF (
        numEntries > 1,
        VAR currentSerial = 'Table 1'[Serial]
        VAR errorCode =
            SELECTCOLUMNS (
                CALCULATETABLE (
                    TOPN (
                        1,
                        'Table 2',
                        'Table 2'[Date/Time], DESC,
                        'Table 2'[Relative Failure Time], DESC
                    ),
                    'Table 2'[Serial] = currentSerial
                ),
                "Error code", 'Table 2'[Error code]
            )
        RETURN
            errorCode
    )

[Anonymous]

Wow thank you for the quick response. 

I tried creating a new column and using that code. 

It gives me an error at: 

        VAR currentSerial = 'Table 1'[Serial]

"A Table of multiple values was supplied where a single value was expected" 

I am very new to Power BI so I am probably doing something wrong. 

[johnt75]

Are you adding this as a column or as a measure ? It needs to be added as a column on Table 1.

[Anonymous]

I am adding it as a column. If I use "New Measure" I have to add a function to the line (MIN,MAX, etc) to get a single value. 

It shows no syntax errors but once I check it, it gives me that table error.

[johnt75]

I think the problem is that there are multiple entries in the Test Bench table with the same date, relative failure time and serial number.

You could either add an index column to that table, and add the index column as another sort option in the TOPN, or instead of using SELECTCOLUMNS you could do either a MINX or MAXX over the TOPN.

[Anonymous]

For this example, the Actual Error Code is reported incorrectly because it chose the earlier date (Row 2) instead of the most recente date (Row 1). I assume it is because Relative Failure Time is larger on Row 2?

[Anonymous]

I have another example as well. It looks like it is not actually selecting the latest date and then the largest relative time.

Is there a way to make it sort Latest Date first then Largest Relative Time? The table is not sorted in order so I don't know if TOPN just looks like the first row. 

[johnt75]

It is sorting by Latest Date first and the Largest Relative Time. I tried using the values you gave in your earlier example and it gave the correct result. Something else must be wrong. Is it possible that other filters are being applied ?

[Anonymous]

I am not sure, I have page filters and stuff but that shouldn't effect how the code runs.

For this serial number, I have 5 different entries. Only 1 entry has a different date, meaning the actual error code for all 5 entries should be 134000. It reports out the actual error code as the impact code associated with each line, so it shows up as:

Actual Error Code:

134000

113152

113161

113152

113161

911001

[johnt75]

can you share a PBIX with any confidential info removed ?

[Anonymous]

I don't think so. I am pulling from an SQL server that requires a login to get the data. I also would have to remove columns that make the report work. 

I looked at the documentation for the TOPN function and it looks like it should be working correctly. It should be pulling all of the rows with the 1 serial number and then looking at the latest date and largest relative time, but based on my results it looks like it is only pulling 1 row. 

My tables include columns not shown but that shouldn't effect TOPN because it only looks at the  columns you tell it to. 

[johnt75]

If there are other columns which are common to both tables that could be causing an issue, as they would be added to the filter context for the CALCULATETABLE. Try adding ALL('Table 2') into the CALCULATETABLE statement

[Anonymous]

There are other columns which are common.

Where exactly would I add the ALL statement inside the CALCULATETABLE function?

[johnt75]

Just before 'Test Bench'[Serial] = currentSerial,

ALL('Test Bench'),
'Test Bench'[Serial] = currentSerial

[Anonymous]

That fixed it, everything is reporting as it should. I really appreciate it.

[johnt75]

Glad its working. Always good to get to the bottom of a problem

[Anonymous]

Oh I see you would be correct. I used Power Query to remove duplicates if they have the same date AND Serial Number AND Relative Failure Time. It fixed it. 

Thank you for being so helpful and responsive.