Can True / False Indicators Be Created Using Rows Instead Of Columns?

[krichmond]

Is there a way to build a "True" / "False" indicator that is driven off information across rows instead of columns? For instance, I want to identify clients (unique ID in a field called "Client ID") within a certain mail month (stored as 1, 2, 3, 4, etc. in a field called "Start Month") that have a "Test" value in a field called "Version Type".

I included this screenshot which I hope helps. For Client ID 102023472 in the month of February (2) there is a row that contains "Test" in the field called "Version Type". So in this case I would want all corresponding rows associated with Client ID 102023472 in the month of February (2) marked with "True". However, for Client ID's 102023487 and 102023489 in the month of February (2), they do not have a row that contains "Test" in the field called "Version Type". So in this case I would want all corresponding rows associated with Client ID's 102023487 and 102023489 in the month of February (2) marked with "False".

[v-tangjie-msft]

Hi @krichmond ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a calculated column.

True_False = 
var _ifexistst=SUMMARIZE(FILTER(mv_perfex,[versiontype]="TEST"),[clientid])
return IF([clientid] in _ifexistst,TRUE(),FALSE())

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[krichmond]

@v-tangjie-msft - This is close but not 100% yet. I think additional criteria needs to be added into the mix above and beyond just Client ID. As you can see from my screenshot, the formula you provided puts TRUE next to everything associated with that Client ID.

What I am trying to do is find instances where a Client ID for a certain Start Month has a row with "Test" under the "Version Type" field. When it finds this, it flags the other items with the same Client ID and Start Month as "Test" as well.

[v-tangjie-msft · Accepted solution]

Hi @krichmond ,

Please try this calculated column:

True_False =
VAR _ifexistst =
    SUMMARIZE (
        FILTER (
            mv_perfex,
            [Start Month] = EARLIER ( mv_perfex[Start Month] )
                && [clientid] = EARLIER ( mv_perfex[clientid] )
        ),
        [versiontype]
    )
RETURN
    IF ( "TEST" IN _ifexistst, TRUE (), FALSE () )

In what form would you like to find data with "Version_type" as "TEST"? You can create a measure and format it conditionally.

Color = IF(MAX('mv_perfex'[versiontype])="TEST","red","black")

Alternatively, you can place the [version_type] field on a slicer to filter the visual, or you can create a measure to place as shown in the following image.

Flag = IF( MAX('mv_perfex'[versiontype])="TEST",1,0)

If you have more detailed requirements for displaying "TEST", please mark the answer to this post as a solution and republish a post detailing your needs, thank you.

Best Regards,

Neeko Tang

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

[krichmond]

@v-tangjie-msft - Bingo! You nailed it. This is perfect. Thank you so much!

[Greg_Deckler]

@krichmond Try:

Measure = 
  VAR __Client = MAX('Table'[Client ID])
  VAR __StartMonth = MAX('Table'[Start Month])
  VAR __Table = SELECTCOLUMNS(FILTER(ALL('Table'),[Client ID] = __Client && [Start Month] = __StartMonth),"Version Type",[Version Type])
  VAR __Result = IF("TEST" IN __Table,TRUE(),FALSE())
RETURN
  __Result

[krichmond]

@Greg_Deckler - I tried that DAX and it didn't return the expected results. "mv_perfex" is the table name so I switched out your "Table" references with that. But outside of that, I didn't adjust anything.

Test Indicator = VAR __Client = MAX('mv_perfex'[clientid]) VAR __StartMonth = MAX('mv_perfex'[startdate]) VAR __mv_perfex = SELECTCOLUMNS(FILTER(ALL('mv_perfex'),[clientid] = __Client && [startdate] = __StartMonth),"Version Type",[versiontype]) VAR __Result = IF("TEST" IN __mv_perfex,TRUE(),FALSE()) RETURN __Result

The below should be true since all of these fall under the same Client ID, in the same Month, and have a Test row.

[Greg_Deckler]

@krichmond Can you post sample data as text?