Optional Parameter usage in DAX

[Surendar88]

I have a following scenario in Power BI Paginated report. Where two parameters are available. One can be blank and one can have value. Below is the DAX  which is giving the result, but it takes more time as it evaluates blank value. Is there any other approach anyone aware of to evaluate only one parameter which has value and ignore the other which is blank. 

DEFINE 
VAR _claim ="ABC"
VAR _policy =""
 

EVALUATE SUMMARIZECOLUMNS 
('Reporting'[Inception Year], 
'Reporting'[PolicyNumber], 
'Reporting'[InsuredName], 

FILTER('Reporting',(_claim= "" ||'Reporting'[ClaimNumber] = _claim)), 
FILTER('Reporting',(_policy= "" ||Reporting'[PolicyNumber] = _policy)), 

"Premium",'Reporting'[Premium])
order by 'Reporting'[Year] desc 

[OwenAuger · Accepted solution]

Hi @Surendar88 

The main improvement I would suggest is to filter on the ClaimNumber and PolicyNumber columns rather than the 'Reporting' table.

Something like:

DEFINE
    VAR _claim = "ABC"
    VAR _policy = ""

EVALUATE
SUMMARIZECOLUMNS (
    'Reporting'[Inception Year],
    'Reporting'[PolicyNumber],
    'Reporting'[InsuredName],
    FILTER (
        ALL ( 'Reporting'[ClaimNumber] ),
        _claim = ""
            || 'Reporting'[ClaimNumber] = _claim
    ),
    FILTER (
        ALL ( 'Reporting'[PolicyNumber] ),
        _policy = ""
            || 'Reporting'[PolicyNumber] = _policy
    ),
    "Premium", 'Reporting'[Premium]
)
ORDER BY 'Reporting'[Year] DESC

There is no way I am aware of to conditionally include/exclude a filter, as conditional tables are not possible.

There could be some other smart approach but this is what I can think of at the moment 🙂

Regards,

Owen

[Surendar88]

@OwenAuger 

Thankyou so much. It works. I am going to incorporate in my report which has few other parameters as well. I will let you know how it goes.