Forum Discussion

DJJR's avatar
DJJR
Frequent Visitor
4 years ago
Solved

Translating an Excel, multi-table, filter formula into a Power BI Table

At our office, we use about 20 standard ID's and Ive been tracking who uses them in an Excel Dashboard.  The ID's are checked in and out by a supervisor in a spreadsheet, so it was simple to create f...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Try this:

    AuditorName =
VAR _Date = 'DPMO Summary'[Date]
VAR _ID = 'DPMO Summary'[Auditor ID]
RETURN
    CALCULATE (
        MAX ( WWID[Name] ),
        REMOVEFILTERS (),
        WWID[WWID] = _ID,
        WWID[Issue Date] <= _Date,
        WWID[Return Date] >= _Date
    )

     

    To get the Picker name, just change the _ID variable to reference the Picker ID column.

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    The most intuitive way would to explicitly check for a blank:

    AuditorName =
VAR _Date = 'DPMO Summary'[Date]
VAR _ID = 'DPMO Summary'[Auditor ID]
VAR _Name =
    CALCULATE (
        MAX ( WWID[Name] ),
        REMOVEFILTERS (),
        WWID[WWID] = _ID,
        WWID[Issue Date] <= _Date,
        WWID[Return Date] >= _Date
    )
RETURN
    IF ( ISBLANK ( _Name ), _ID, _Name )

     

    You could also use COALESCE for the final line

    COALESCE ( _Name, _ID )
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

It's hard to tell without seeing your tables and how they're named and related.

 

Making some assumptions, I'd expect the DAX equivalent to be something like this:

SelectName =
VAR _Date = Production[Date]
RETURN
    CALCULATE (
        MAX ( WWID[Name] ),
        WWID[Issue Date] <= _Date,
        WWID[Return Date] >= _Date
    )

But I can't really tell from the information you've provided.

DJJR's avatar
DJJR
Frequent Visitor
4 years ago

Thanks for your help with this. I am missing a way to reference the WWID in the Summary Table with the WWID Table. I need a seperate name to populate in the Auditor and Picker Columns.

 

 

 

 

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

    Try this:

    AuditorName =
VAR _Date = 'DPMO Summary'[Date]
VAR _ID = 'DPMO Summary'[Auditor ID]
RETURN
    CALCULATE (
        MAX ( WWID[Name] ),
        REMOVEFILTERS (),
        WWID[WWID] = _ID,
        WWID[Issue Date] <= _Date,
        WWID[Return Date] >= _Date
    )

     

    To get the Picker name, just change the _ID variable to reference the Picker ID column.

    • DJJR's avatar
      DJJR
      Frequent Visitor
      4 years ago

      Worked like a charm. Thanks for your help.

    • DJJR's avatar
      DJJR
      Frequent Visitor
      4 years ago

      One more question. Some of the values return show up as blank. How would you rewrite this to default back to the auditor ID if the Name wasn't found.

       

      Thanks.

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

        The most intuitive way would to explicitly check for a blank:

        AuditorName =
VAR _Date = 'DPMO Summary'[Date]
VAR _ID = 'DPMO Summary'[Auditor ID]
VAR _Name =
    CALCULATE (
        MAX ( WWID[Name] ),
        REMOVEFILTERS (),
        WWID[WWID] = _ID,
        WWID[Issue Date] <= _Date,
        WWID[Return Date] >= _Date
    )
RETURN
    IF ( ISBLANK ( _Name ), _ID, _Name )

         

        You could also use COALESCE for the final line

        COALESCE ( _Name, _ID )