Forum Discussion
Convert column to measure
I have a text column, Durability, that corresponds to risk ratings, for example:
'Durable' = 0
'Normal' = 1
When I try to use IF, the Durability column is not selectable. For example, the following doesn't work:
Durability Risk = IF([Durability]="Durable",0,1)
Even if I use Power Query to create the Durability Risk score, it also can't be surfaced.
How do I create a DAX measure from the text column to combine with other risk measures that I have already created from summaries of other tables?
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.
9 Replies
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))
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.Thanks for the example. That would work great if the scale only had two items. I simplified for the purposes of asking the question. I'll keep that in mind for the future.
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])
