Create a calculated column to get all values as True if a word is contained in another column

[Anonymous]

Hello community,

I would like to create a calculated column to give me a true value for all elements if a word is contained inside and use it later as a filter to select records that have that word or not. I have a screen shot of a table with some results. I did created a column :MDR" to give me 1 if MDR word existd in Subtype column, but I want to get all values as "1" if MDR word exists in Subtype column and "0" or false or "No MDR" otherwise. Here it shows "1" only for "MDR" values, and I want "1" for "LSD", "NP",... also. Any help is much appreciated. Thank you

[Adescrit]

Hi @Anonymous 

Does this work for you:

MDR =
VAR __CountMDR =
    CALCULATE (
        COUNTROWS ( 'Table (2)' ),
        ALL ( 'Table (2)' ),
        'Table (2)'[Subtype] = "MDR",
        'Table (2)'[Number] = EARLIER ( 'Table (2)'[Number] )
    )
RETURN
    IF ( __CountMDR > 0, 1, 0 )

[Anonymous]

I tried the formula, but it seems that the Earlier function doesn't like the column and parameter type. I guess it should be a RELATED function somewhere.

[Adescrit]

Hi @Anonymous ,

If the two columns are in different tables, but what you want to do is display the result 1 or 0 in a table visual with the numbers and subtypes, you could try this formula for a measure:

MDR = 
VAR __Num = SELECTEDVALUE( Assets_extended[id_regnum] )
VAR __CountMDR = CALCULATE( COUNTROWS( Artifacts_extended ),
    ALL( Artifacts_extended ),
    Artifacts_extended[Subtype] = "MDR",
    Assets_extended[id_regnum] = __Num
)
RETURN
    IF ( __CountMDR > 0, 1, 0 )

[Anonymous]

Hi @Adescrit ,

I would like to create a column so I can use it as filter to find Assets with MDR or Assets without MDR. If the resulting column is showing 0 for Assets without MDR (all subtypes) and 1 for assets with MDr (all subtypes), I can filter it. 

For example asset __888 doesn't have MDR, so MDR test 1 should show 0 for all 7 subtypes. Asset ---4428 has MDR, so MDR test 1 should show 1 for all five subtypes, __00446 should show 0, __4150 should show 0. Hope you understand the requirement. The measure is not helpful.

[Adescrit · Accepted solution]

Hi @Anonymous 

You can add this column to the Assets_extended table and it should return 1 for each asset that contains an "artifact" with a subtype of MDR. You can then use that column to filter the rest of the dataset. Make sure you set the Summarization to "Don't Summarize" for it to display correctly in a table or matrix visual (if you need to add it to one).

MDR = 
    VAR __Num = [Number]
    VAR __MDRCount = CALCULATE( COUNTROWS( Artifacts_ext ), Artifacts_ext[Subtype] = "MDR" )
RETURN
    IF( __MDRCount > 0 , 1, 0)

Alternatively if you want to create the column on the artifacts_ext table then you can try this (however I'm not sure you would be able to apply filters on your dataset based on your current model and filter directions):

MDR = 
VAR __Num = RELATED(Assets_extended[Number])
VAR __MDRCount = CALCULATE( COUNTROWS( Artifacts_ext ), ALL(Artifacts_ext), Assets_extended[Number] = __Num, Artifacts_ext[Subtype] = "MDR" )
RETURN
    IF( __MDRCount > 0, 1, 0 )

[Anonymous]

Thank you @Adescrit,

It will do the trick. Appreciate your prompt responses. Accepted as solution

[Anonymous]

Hi Adescrit, thank you for replying. I would like to mention that the columns shown are from different tables and MDR is the column I created (tried to solve the problem). The tables are linked like this, Events is in the middle. This might change the structure of the formula. What I intended to do is to be able to select which [Number} -unique value has a "MDR" subtype attached to it. If the new column can show "Yes" or "1" for all the subtypes with the same Number, that I can filter and solve the issue. Hope this helps.