Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SwaroopShankar
Frequent Visitor

measure if column value is a certain text

Hi,

 

Please see below data for your refernce.

 

Annotation 2020-03-20 200947.png

 I need to get a Measure which concatenates 'Pic' column based on 'Name'.

 

Basically, IF('Name'="Site Photo 1", CONCATENATE('Pic') using 'Index').

 

I have been wrecking my head with this problem for hours. Please advise. 

Any help is much appreciated. 

1 ACCEPTED SOLUTION

Hi my friend, you can apply filter you need, for example if you try this:

 

MyMeasure = 
IF (   
    SELECTEDVALUE('Table'[Name]) = "My Photo1.png",    
    CONCATENATEX (
        'Table',
        'Table'[Pic],
        "",
        'Table'[Index], ASC
    ),""
)

 

When you have 4 values it will fill the concatenation only if selected Name is "My Photo1.png":

Solution98_2.png

So you will get the same behavior if you try with an slicer and a card, the card will fill the concatenation only if the name "My Photo1.png" is selected.

 

Give it a try and let me know if it works.

 

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

 

PS: There is a current contest in this community I'm participating in, if you like it I'll appreciate your Kudo in this link:

 

Better-Together-T-Shirt-Contest 

 

Regards,

 

Gian Carlo Poggi

View solution in original post

6 REPLIES 6
gpoggi
Responsive Resident
Responsive Resident

Hi SwaroopShankar,

 

It is not complicated, just create a measure like this:

 

MyMeasure = 
IF (
    HASONEVALUE ( 'Table'[Name] ),
    CONCATENATEX (
        'Table',
        'Table'[Pic],
        "",
        'Table'[Index], ASC
    )
)

 

 

And you will see the desired result:

 

Solution98.png

 

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

 

PS: There is a current contest in this community I'm participating in, if you like it I'll appreciate your Kudo in this link:

 

Better-Together-T-Shirt-Contest 

 

Thanks in advance,

 

Regards,

 

Gian Carlo Poggi

Hi Gpoggi,

 

Thanks for trying to help. Your solution works if only 1 image is present in the folder.

I have multiple image files in the same folder. I want a measure that concatenates the strings in 'pic' ONLY if the 'name' matches 'Site Photo 1' (or whatever I mention).

I can control the naming convention of the images, so I can hardwire the 'name' into the measure. 

Anonymous
Not applicable

Hi @SwaroopShankar,

I guess you are doing something like this?
https://blog.crossjoin.co.uk/2019/05/19/storing-large-images-in-power-bi-datasets/

Sorry, I can't test it at the moment, but why not to use Dax GroupBy? I think it will do what you need.

Kind regards,
JB

Hi my friend, you can apply filter you need, for example if you try this:

 

MyMeasure = 
IF (   
    SELECTEDVALUE('Table'[Name]) = "My Photo1.png",    
    CONCATENATEX (
        'Table',
        'Table'[Pic],
        "",
        'Table'[Index], ASC
    ),""
)

 

When you have 4 values it will fill the concatenation only if selected Name is "My Photo1.png":

Solution98_2.png

So you will get the same behavior if you try with an slicer and a card, the card will fill the concatenation only if the name "My Photo1.png" is selected.

 

Give it a try and let me know if it works.

 

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

 

PS: There is a current contest in this community I'm participating in, if you like it I'll appreciate your Kudo in this link:

 

Better-Together-T-Shirt-Contest 

 

Regards,

 

Gian Carlo Poggi

edhans
Super User
Super User

Is this really a measure, or is it a calculated column, or is this a custom column in Power Query?

 

I'm not a programmer. What does  CONCATENATE('Pic') using 'Index' do? what is your expected output?

 

To just combine fields in Power Query, you just use [field1] & [Field2]

 

DAX is different, but need to understand the terms you are using. And you did post this in the Power Query forum, so that is why your use of the word "measure" has thrown me a bit.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

My apologies for the confusion. I am fairly new to the Power BI community.

 

I need the end result to be a Measure and not a column.

 

CONCATENATE combines 2 or more values together. I want the order in which it compiles these values to follow the 'Index' values (small to big)

 

I can not use [Field 1] & [Field 2] technique because I have a lot of these values and the number of values change based on the image size. 

 

Please help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors