cancel
Showing results for 
Search instead for 
Did you mean: 
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
Advocate V
Advocate V

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. 

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

View solution in original post

edhans
Super User III
Super User III

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors