cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrupsidown
Helper I
Helper I

Compute column based on values of "similar" rows

mrupsidown_1-1626435652194.png

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.

16 REPLIES 16
mwegener
Super User II
Super User II

Hi @mrupsidown ,

 

try this.

 

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

Hi @mrupsidown ,

 

you used the DAX statement for a calculated column?

ComputedColumn.png

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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.

DAX-computed.png

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.

 

MeasureColumn.png

 

The statement was written for 2. DAX Calculated Column.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

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")

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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!).

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

Hide_Field.png

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

mrupsidown_1-1627366209335.png

 

@mrupsidown do you have the view hidden option active?

View_Hidden.png

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

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

ValuesField.png

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

 

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

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.