Forum Discussion

MRad's avatar
MRad
Regular Visitor
2 years ago
Solved

Overload resolution failed because no accessible 'IIf' accepts this number of arguments.

Hello everyone:

I am building Paginated Report in PBI and would like to have a Dynamic Report Title.  All my components work fine but put together gives an error stating that IIF cannot take too many arguments.  I am very new to PBI and would be grateful for any help.  In business terms what I am saying is if user does not enter the paraemter than use the first condition else second conidtion:

=IIf(Parameters!FromDate.Value IsBlank,

"Cost from " + IF(MONTH(TODAY()) <= 6,Year(TODAY()) -1 &"/07/01",Year(TODAY()) &"/07/01")
+ " to " + IF(MONTH(TODAY()) <= 6,Year(TODAY()) & "/" &RIGHT("00"& MONTH(TODAY()),2)&"/01", Year(TODAY()) -1 & RIGHT("00"& MONTH(TODAY()),2)&"/01")
,
"Cost from " +FormatDateTime(Parameters!FromDate.Value, DateFormat.ShortDate) +" to "+ FormatDateTime(Parameters!ToDate.Value, DateFormat.ShortDate)
)

 

MR

  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from hackcrr , allow me to provide a supplement to the approach you gave:
    Hi,MRad 

    Regarding the issue you raised, my solution is as follows:

     According to the screenshot of your error report, the main reason is that the citation isbank function can not be called, the reason for this situation is because you use the function of DAX in powerquery, in Power Query M language, there is no direct ISBLANK function, but you can use other methods to determine whether it is empty or not:
    1. Table.IsEmpty().To check if the table is empty, here is a simple output:

    Below are links to the relevant documentation:

     Table.IsEmpty - PowerQuery M | Microsoft Learn
    2.List.IsEmpty().Checks if the column is empty, here is a simple output:

    Below are links to the relevant documentation:

    List.IsEmpty - PowerQuery M | Microsoft Learn

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi, MRad 

    In a Power BI DAX or M query, you should use IF instead of IIf, and you may want to use the & operator in string concatenation. Here is a modified expression:

    = IF(  
    ISBLANK(Parameters!FromDate.Value),  
    "Cost from "   
    & IF(MONTH(TODAY()) <= 6,   
        FORMAT(DATE(YEAR(TODAY()) - 1, 7, 1), "yyyy/MM/dd"),   
        FORMAT(DATE(YEAR(TODAY()), 7, 1), "yyyy/MM/dd")  
    )   
    & " to "   
    & IF(MONTH(TODAY()) <= 6,   
        FORMAT(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), "yyyy/MM/dd"),   
        FORMAT(DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), "yyyy/MM/dd")  
    ),  
    "Cost from "   
    & FORMAT(Parameters!FromDate.Value, "yyyy/MM/dd")   
    & " to "   
    & FORMAT(Parameters!ToDate.Value, "yyyy/MM/dd")  
)

     

    Best Regards,

    hackcrr

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MRad's avatar
      MRad
      Regular Visitor
      2 years ago

      Thank you hackcrr for taking the time to respond.  I am sorry if I

      did not mention the report is in Power BI Report builder and running your code I got this error:ISBLANK is not declared

       

       

      Appreciate your help

      • MRad's avatar
        MRad
        Regular Visitor
        2 years ago

        Any chance anyone could shed more light on this, would very much appreciate it, still have not resolve it, thank you