Compute column based on values of "similar" rows

[mrupsidown]

How would I go about computing the values of the "computed" column based on the following rules:

• At least one of the rows (here 2) with id 123 has "A" as the value of the "field x" so it should display "YES" in the "computed" column for all rows with id 123.
• Same for id 124.
• None of the rows with id 125 have "A" as the value of the "field x" so it should display "NO" in the "computed" column.

Thanks in advance.

[mwegener]

Hi @mrupsidown ,

try this.

computed =
IF (
    CALCULATE ( MIN ( 'Table'[field x] ), REMOVEFILTERS ( 'Table'[field x] ) ) = "A",
    "YES",
    "NO"
)

[mrupsidown]

That does NOT work. It will display "YES" for rows that have "A" and "NO" for other rows with the same id.

[mwegener]

Hi @mrupsidown ,

you used the DAX statement for a calculated column?

[mrupsidown]

Yes I have used DAX. The only difference is that my "table" is a Query. Again, it will display "YES" for rows that have "A" and "NO" for other rows with the same id which is not my goal.

I don't even know how your example works without specifying the ID anywhere?

Adding a screenshot where you can see my table, column names and values (some stuff is blurred because I can't show this information) and the DAX function and its result. Instead of "A" which was an example, it must match the value "Global". Blurred rows have other values than "Global" in the "Modèle" column.

[mwegener]

Hi @mrupsidown ,

I am not sure if we have understood each other correctly.
My question was whether the DAX Statement as a
1. DAX Measure
or
2. DAX Calculated Column
was used.

The statement was written for 2. DAX Calculated Column.

[mrupsidown]

I select my table and click on "New column".

[mwegener]

If there are more columns, you can also try this one.

computed = 
IF(CALCULATE(MIN('Table'[field x]),ALLEXCEPT('Table','Table'[id])) = "A", "YES", "NO")

[mrupsidown]

That doesn't work either. I was able to achieve what I wanted with the solution provided here: https://stackoverflow.com/a/68210381/1238965 but it uses an index column which is fine, except that there is apparently no way in PowerBi to hide a column in a table (yayy!).

[mwegener]

Hi @mrupsidown , you can hide columns in the field list.

[mrupsidown]

True. Somehow they still appear in my table after I hit "Hide", any idea?

[mwegener]

@mrupsidown do you have the view hidden option active?

[mrupsidown]

Yes. And if I unselect "View hidden" then they disappear from the fields list. Not from the table...

[mwegener]

For this you have to remove the columns from the values field of the table visual.

[mrupsidown]

No. Obviously this REMOVES the fields. I want to HIDE them, not REMOVE them. I need some fields for some calculations / bookmarks, but I don't want them to be DISPLAYED in my table.

Apparently, feature requests from YEARS ago, with HUNDREDS of votes still can't be implemented for whatever reason.

https://ideas.powerbi.com/ideas/idea/?ideaid=5f080970-9ddc-4d8b-8e51-8519eef2ce60

[v-xiaotang]

Hi @mrupsidown 

Have you solved this problem? If not, could you add more details?

I've tested the measure mentioned by mwegener, and it works! also, I don't think an index column is needed there.

besides, you don't need to worry that value can't be used for calculation if it's not in the table. It depends on the specific situation.

-

If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution 😁). so the others can find it more quickly.
really appreciate！

-
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

[mwegener]

I'm not sure if the fields really need to be present in the table visual to meet your requirements.
Can you describe/explain this more?

DAX calculations can be made based on fields that are not displayed in the visual. Also the visual can be filtered on fields that are not displayed in the visual.