Forum Discussion

bonjourposte's avatar
bonjourposte
Helper V
3 years ago

Nested IF statement?

I'm following Dapper Dash' tutorial to get images in Power BI.  I've converted the .jpg images to base 64 according to his third method, and i've gotten up to 33:15.  https://www.youtube.com/watch?v=W0KyQ6w_-nI&t=1895s

 

The code he provides DOES work- I click on a filter and it shows the appropriate picture of the corresponding location, but when nothing is filtered, I get this error message:

 

There are about a thousand locations, but I only have 200 location images, so I need a placeholder for when i don't have a picture in the folder, or when no location is filtered.  So I have a generic image to display. 

 

Here is his code: 

Image =
var img =
IF(
    HASONEVALUE('City Images JPG Base 64'[Name]),
    "data:image/jpg;base64, " &
    CONCATENATEX('City Images JPG Base 64',
    'City Images JPG Base 64'[Pic],
    ,
    'City Images JPG Base 64'[Index],ASC)
)

return img

 

 

I want to add an "else" clause or a nested IF.  Do I have to give CONCATENATEX instructions all again the second time?  What would the syntax be?  I've gotten as far as this, but it doesn't work:

 

Image =

var img =

IF(

        HASONEVALUE('City Images JPG Base 64'[Name]),

        "data:image/jpg;base64, " &

        CONCATENATEX('City Images JPG Base 64',

        'City Images JPG Base 64'[Pic],

        "",

        'City Images JPG Base 64'[Index],ASC            

        ),

        CONCATENATE('City Images JPG Base 64',

        'City Images JPG Base 64'[Index],

        ("", "495","496","497"),

        'City Images JPG Base 64'[Pic],ASC

        )

)

        Return img

 

"Index" is the row number in the table; "Pic" is the base64 code; and "Name" is the name of the location (which is also the name of the image document).  The base 64 code that I want is on rows 495, 496, 497.  (Although I just moved it to 0, 1, and 2.)

Need Help

12 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    3 years ago

    I split the code in two conditions : 

    • If there is only one filter applied and there is a matching image in the table, display the image.
    • If there is no filter applied or no matching image in the table, display a placeholder image.

     

    Image =
    VAR img =
    IF (
    HASONEVALUE ( 'City Images JPG Base 64'[Name] ),
    "data:image/jpg;base64, "
    & CONCATENATEX (
    'City Images JPG Base 64',
    'City Images JPG Base 64'[Pic],
    "",
    'City Images JPG Base 64'[Index], ASC
    ),
    IF (
    COUNTROWS ( 'City Images JPG Base 64' ) = 0
    || ISBLANK ( 'City Images JPG Base 64'[Pic] ),
    "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mP8v5+jwAHuwM+g5t5ewAAAABJRU5ErkJggg==",
    "data:image/jpg;base64, "
    & CONCATENATEX (
    'City Images JPG Base 64',
    'City Images JPG Base 64'[Pic],
    "",
    'City Images JPG Base 64'[Index], ASC
    )
    )
    )
    RETURN
    img

    NB :  I used base64-encoded PNG image as the placeholder. You can replace it with your own placeholder image in the same format as your other images.

    • bonjourposte's avatar
      bonjourposte
      Helper V
      3 years ago

      Thanks, Amira!  I think my base64 code is around 90,000 characters, though.  Is it still appropriate to put it in this function?

      • AmiraBedh's avatar
        AmiraBedh
        Super User
        3 years ago

        It is possible but the recommendation is to use smaller Base64 codes in DAX functions whenever possible

  • bonjourposte's avatar
    bonjourposte
    Helper V
    3 years ago

    So I was fiddling with the code today and tried every single variation of ISBLANK/COUNTROWS/II/&&/all the possible column names and table names, but nothing worked.  

     

    When I removed ‘City Images JPG Base 64’[Selected Image]) from the ISBLANK function, the whole image disappeared and it said “Fix this.”  When I put it back in, it worked again.  So the problem must be with the COUNTROWS function.  If I take out COUNTROWS(‘City Images JPG Base 64’) altogether, it still works to the same degree.   

     

    Image =

    var img =

    IF([SelectedImage]="Canada" ||

        COUNTROWS('City Images JPG Base 64')=0 && ISBLANK('City Images JPG Base 64'[SelectedImage]),

        "data:image/jpeg;base64, "&

        CONCATENATEX(FILTER

        ('City Images JPG Base 64',

        'City Images JPG Base 64'[Name]="Canada.jpg"),'City Images JPG Base 64'[Pic],,'City Images JPG Base 64'[Index],ASC),

    IF(

        HASONEVALUE('City Images JPG Base 64'[Name]),

        "data:image/jpg;base64, " &

        CONCATENATEX

            ('City Images JPG Base 64',

            'City Images JPG Base 64'[Pic],

            ,

            'City Images JPG Base 64'[INDEX],ASC)

        )

    )

     

    RETURN img

     

     

    So then I actually removed the 4th line altogether (ISBLANK...) and the code worked the same as it did without that line!! 

     

     
    Image =
    var img =
    IF([SelectedImage]="Canada",
        "data:image/jpeg;base64, "&
        CONCATENATEX(FILTER
        ('City Images JPG Base 64',
        'City Images JPG Base 64'[Name]="Canada.jpg"),'City Images JPG Base 64'[Pic],,'City Images JPG Base 64'[Index],ASC),
    IF(
        HASONEVALUE('City Images JPG Base 64'[Name]),
        "data:image/jpg;base64, " &
        CONCATENATEX
            ('City Images JPG Base 64',
            'City Images JPG Base 64'[Pic],
            ,
            'City Images JPG Base 64'[INDEX],ASC)
        )
    )

    RETURN img