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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RYU209
Helper III
Helper III

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

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. 

1 ACCEPTED SOLUTION
eliasayy
Impactful Individual
Impactful Individual

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 

eliasayy_0-1673569414156.png

 

 

 

 

 

 

View solution in original post

7 REPLIES 7
eliasayy
Impactful Individual
Impactful Individual

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 

eliasayy_0-1673569414156.png

 

 

 

 

 

 

@eliasayy 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!

eliasayy
Impactful Individual
Impactful Individual

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

eliasayy_0-1674086698276.png

and in the picture, you can see the filter applied

SivaMani
Resident Rockstar
Resident Rockstar

@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

 

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

@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

SivaMani_0-1673554359663.png

SivaMani_1-1673554424459.png

 

 

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, ... 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.