topic Re: Optional Parameter usage in DAX in DAX Commands and Tips

Optional Parameter usage in DAX

Surendar88 — Wed, 13 Jul 2022 23:57:32 GMT

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

Re: Optional Parameter usage in DAX

OwenAuger — Fri, 15 Jul 2022 01:27:06 GMT

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

Re: Optional Parameter usage in DAX

Surendar88 — Fri, 15 Jul 2022 03:42:19 GMT

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.