Only Show Values that previously existed but with different column criteria (Direct Query)

[RYU209]

Hello,

I have two columns: Transaction and Flag 

Transaction can have numerous duplicates and Flag will be either "A" or B". A sample table could look like this.

Transaction      Flag
1                      A
1                      A
1                      B
2                      A
2                      B
3                      B

I need to create a Table visualization that shows all Transactions that have a Flag for both "A" and "B" but the visualization must be filtered so that only the records with "B" flags show up.

For instance, the last row in the example table would not show up in the visualization because there's no record of transaction 3 having an "A" flag.  

The resulting visualization would look like:

Transaction      Flag
1                      B
2                      B

Can anyone help here? Keep in mind I am using Direct Query so some options are limited. 

[Anonymous · Accepted solution]

hello you can try to concatenate by follwoing:
first create a new meausre:

Measure =

 VAR concat = CONCATENATEX(FILTER(ALL(Table2),MAX(Table2[Transaction])=Table2[Transaction]),Table2[Flag]," ")

 VAR cond = IF(AND(CONTAINSSTRING(concat,"A"),CONTAINSSTRING(concat,"B")),"True","False")

 Return

 cond


OR

Measure =

 VAR dist = CALCULATE(DISTINCTCOUNT(Table2[Flag]),ALLEXCEPT(Table2,Table2[Transaction]))

 VAR cond = IF(dist > 1,"True","False")

 Return

 cond

then on you filter panel drag the meassure and press on is and type True 

[RYU209]

@Anonymous Thank you! I forgot to mention there is a third column "Material". I need to filter the Table visual not only on Transactions that have a record of both "A" and "B" in the Flag column (and only show B) but also records where the Material column did not change after the flag has changed. There are also other columns that would be different after the Flag has changed from A to B which will also be in the table visual. 

Transaction      Flag       Material
1                      A            Steel
1                      A            Wood
1                      B            Steel
1                      B            Plastic
2                      A            Plastic
2                      B            Plastic
3                      B            Wood

Filtered Table:

Transaction      Flag       Material
1                      B            Steel
2                      B            Plastic

Thanks again for your help!

[Anonymous]

im not sure if i understood you well but please try:

measure =

 VAR dist = CALCULATE(DISTINCTCOUNT('Table'[Flag ]),ALLEXCEPT('Table','Table'[Transaction ],'Table'[ Material]))

 VAR cond = IF(dist > 1,"True","False")

 Return

 cond

and in the picture, you can see the filter applied

[SivaMani]

@RYU209, Create the below measure,

UniqueFlagCount = CALCULATE(
    SUMX(
        VALUES('Table'[Flag]),
        1
    ),
    ALL('Table'[Flag])
)

Use this measure as a visual lever filter. UniqueFlagCount > 1

[RYU209]

Hmm this isn't working for me. Can you walk me through what's going on in the formula? 

[SivaMani]

@RYU209 The formula gives you the distinct count of Flag and ignores filters on the Flag column only for this calculation

It is working for the scenario you mentioned

[RYU209]

Hi SivaMani,

Sorry I forgot to mention that the Flag has more than 2 values and is dynamic. I'm not sure at any given time how many unique flags there will be; however the logic is only dependant on flags A and B. 

So one transaction could have multiple rows with flags A, B, C, ...