Show all entries of the same number where there exists an "-" and a "+"

[Laura1996]

I have a table with thousands of entries with material numbers (Material) - those Material has certain movement, which is shown in "BwA". So every number stands for some certain movement in the company. For the evaluation I care just about the "261". Now I want to find the Material that have a 261 in "-" AND in "+". All the other Material that have a 261 in either "+" OR "-" should not be displayed. I tried it in DAX but can't figure out how to do it.

Thanks in advance 🙂

[FreemanZ]

hi @Laura1996 

You may also try to add a Tag column like this:

Tag = 
VAR _material = [Material]
VAR _list = 
CALCULATETABLE(
    VALUES(TableName[BewertArt]), 
    TableName[Material] = _material
)
RETURN
IF(
    [BwA]=261 &&  COUNTROWS(_list)=2,
    "Yes", "No"
)

then you can filter any visual with this column. 

[Laura1996]

Hey 🙂 

Thank you for your response - as I discovered some issues with the marked solution I wanted to ask how I can add the "+" and "-" in your formula? 

[AlB · Accepted solution]

Hi @Laura1996 

1. Place Table1[Material] in a table visual

2. Create this measure

ShowMeasure =
VAR hasPlus_ = CALCULATE(COUNT(Table1[Material]), Table1[BwA] = 261, Table1[BewertArt] = "+") > 0
VAR hasMinus_ = CALCULATE(COUNT(Table1[Material]), Table1[BwA] = 261, Table1[BewertArt] = "-") > 0
RETURN
IF( hasPLUS_  && hasMinus_, 1,0)  

Note that if Table1[BwA] is of type text instead of whole number, you'll need to use

     Table1[BwA] = "261"

in the code above

3. Apply [ShowMeasure] as a visual filter to the table visual, choosing to show when value is 1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Laura1996]

Thank you for the fast solution 🙂

This one works for me - however just on the original table. If I use it on the aggreated data (where I have also some "LOOKUPVALUE") it shows me a wrong result. any idea what could cause this?

Thanks a lot 🙂

[AlB]

@Laura1996 

I'll need some more details, ideally some sample data, of where it does not work. I do not understand what you mean

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Laura1996]

I found the mistake - PowerBI was automatically summing up the numbers, which I didn't wanted. Spend some time searching for the mistake, adapted further and now everything works. Thank you so much for the solution & the offer to help 🙂