Forum Discussion
Convert column to measure
If you want to aggregate the risk, wrap the function in an iterator
Given the Table is called Test, the function I provided gives the total durable score (not ratio) based on a Durability field which has a Text value of either "Durable" or "Normal" as per the question.
Durability Risk = SUMX(Test,IF(Test[Durability]="Durable",0,1))
After adding a 1:1 relationship between Supplier Namers and Suppliers, I can use
Durability Risk = SUM(Suppliers[Durability Risk])
I don't understand why I can't add a number column that is available in Supplier Names to other measures I created, though. It seems convoluted to have to reference the value that way.
Hi jiwhite,
=> When I try to use IF, the Durability column is not selectable.
In your scenario you are using measure. Right? You should know that when using a measure, it is unable to call table column directly. The columns should be wrapped with aggregate functions. If you use your formula in a calculated column. It will work fine without any issue.
Please refer to following calculated column:
Then you want to count the Durability when Durability = 'Normal'. Right? To achieve it will measure, you can refer to following expression:
SUM Durability Risk = CALCULATE ( COUNT ( Suppliers[Durability Risk] ), FILTER ( Suppliers, Suppliers[Durability Risk] = "Normal" ) )Thanks,
Xi Jin.
Looks like you're missing your table/sheet name. The calculated column should look instead like:
Durability Risk = IF('Table Name'[Durability]="Durable",0,1) and format the data as a whole number. You can then use the value(s) in a measure such as
Risk Percentage = CALCULATE(SUM('Table Name'[Durability Risk])/COUNTROWS('Table Name'))
After adding a 1:1 relationship between Supplier Namers and Suppliers, I can use
Durability Risk = SUM(Suppliers[Durability Risk])
I don't understand why I can't add a number column that is available in Supplier Names to other measures I created, though. It seems convoluted to have to reference the value that way.
To access the value of a column across a relationship either from the Many to the one or One-to-One, use RELATED()
In SupplierName table
New Column = Related(Supplier[Durability])
If I try to use RELATED instead of SUM, I get:
The column 'Suppliers[Durability Risk]' either doesn't exist or doesn't have a relationship to any table available in the current context.
Can you provide a little more data? In the original post, you mentioned that you have a text column called Durablity.
1) On which table does the column [Durability] reside?
2) You mentioned a relationship between Supplier table and SupplierName table.
3) On which table were you trying to put the measure: Durability Risk = IF([Durability]="Durable",0,1) ?
The issues you are seeing have to do with relationships, and I am trying to understand your structure.