Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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. |
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.
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?
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. |
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 🙂
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. |
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
48 | |
45 | |
19 | |
16 |