Forum Discussion
Translating an Excel, multi-table, filter formula into a Power BI Table
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 )
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.
Super UserTry 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.
Worked like a charm. Thanks for your help.
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
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 )