Forum Discussion
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...
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.
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 )
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
Super User
Super UserThe 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 )