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

[DJJR]

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 formula that filters by date to show who was using it. I have both the production report and ID table linked to the Power BI report just like the excel, but I can not figure out how to pull the name from that ID report like I did before. The Excel Formula I used was

 =@FILTER(WWID[Name],(WWID[Issue Date]<=[@Date])*(WWID[Return Date]>=[@Date])*(WWID[[WWID ]]=[@[Auditor ID]]),[@[Auditor ID]])

[AlexisOlson · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[DJJR]

The tables below are the what I am working with. I am trying to return the proper names in the Auditor and Picker Columns on the first tqable. 

 

[AlexisOlson]

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]

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 · Accepted solution]

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]

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 · Accepted solution]

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 )

[DJJR]

Worked like a charm. Thanks for your help.