Nested IF statement?

[bonjourposte]

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.)

[bonjourposte]

This is the relationship screen.  

[bonjourposte]

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

[AmiraBedh]

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]

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

[AmiraBedh]

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

[bonjourposte]

Hi, I tried your code, but instead of the 90,000 characters of Base 64, I just wrote the name of the picture in the folder.  It didn't like that- how would you reference the image if not by the code or the title?

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],

        "",

        "data: image/jpg;base64, " &

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

        ),

    IF(

        COUNTROWS('City Images JPG Base 64')=0

        || ISBLANK('City Images JPG Base 64'[Pic]),

        "data: image/jpg;base64,AA Canada generic",

        "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

[bonjourposte]

So I fiddled with this some more and found some more documentation from Dapper Dash and combined it a bit with your suggestion- I added a measure to my "City Images JPG Base 64" data set that checks to see if a filter has been applied.  Then I added the code at the beginning (box 1) for  instances when nothing is selected.  It works.  

But then I added an else clause at the end for images that don't have a picture (box 2) and it's not returning the default image.  Suggestions?

[AmiraBedh]

Can you share your pbix file ?

[bonjourposte]

Unfortunately not, but here is a picture of my dashboard: 

The bar chart (FSA) acts as the filter for the map (FSA) and the image in the circle (the city that the FSA is in).  (FSA is the first three letters of our postal code)

I have a key connecting my FSA's to Cities.  it's called "FSA to City Key"

In the relationships screen, the "FSA to City Key" is connected indirectly to the main data table "2021 FSA API data" via the orange donut around the circle.

Then I have another table with all the base 64 images of the cities.  It's called "City Images JPG Base 64":

The first column is "Name", the second column is "Pic", and the third column is "Index".  

Then on the home screen, I have a measure attached to the "City Images JPG Base 64" table called "Selected Image". 

This is the measure: 

SelectedImage = SELECTEDVALUE('FSA to City Key'[City],"Canada")

The top measure connected with this table is the one I need help with; it's called "Image".  This is the latest version of it.  The First two "IF" statements work, it's just the else part of the second "IF" statement that I need help with.

Image =

var img =

IF([SelectedImage]="Canada" ||

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

    "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),

                "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)

    )

)

RETURN img

Thank-you for your time.  

[AmiraBedh]

You can put all your images in a shared folder (repository) and you can reference them by link

[bonjourposte]

You're suggesting I do that so I can securely post my dashboard online?  Or to resolve the concatenate issue?  The concatenating works fine, it's just the code.

[bonjourposte]

Hi Amira, I recreated the file so it was shareable, but how do I share it now?  I assumed I could upload it, but I don't see a button for that.