Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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.

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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
    )

17 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

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