Forum Discussion

Surendar88's avatar
Surendar88
Frequent Visitor
4 years ago
Solved

Optional Parameter usage in DAX

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's avatar
    OwenAuger
    4 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Surendar88
      Frequent Visitor
      4 years ago

      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.