cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jiwhite Regular Visitor
Regular Visitor

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?

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
BraneyBI Member
Member

Re: 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))

jiwhite Regular Visitor
Regular Visitor

Re: Convert column to measure

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.

v-xjiin-msft Super Contributor
Super Contributor

Re: Convert column to measure

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:

 

3.PNG

 

 

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 9
BraneyBI Member
Member

Re: 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))

nchambe Regular Visitor
Regular Visitor

Re: Convert column to measure

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

 

jiwhite Regular Visitor
Regular Visitor

Re: Convert column to measure

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.

BraneyBI Member
Member

Re: Convert column to measure

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

jiwhite Regular Visitor
Regular Visitor

Re: Convert column to measure

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.

BraneyBI Member
Member

Re: Convert column to measure

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.

jiwhite Regular Visitor
Regular Visitor

Re: Convert column to measure

Durability and Durability Risk are originally on Suppliers, which is the list of Suppliers that have had quality assessments.  There is one entry per supplier in Suppliers. Supplier Names is a query of Suppliers that extracts the Supplier Name, Supplier ID, Durability, and Durability Risk. Suppliers has a 1:1 relationship with Supplier Names.  On Supplier Names, I have added risk measures from other tables, for example, % shipments received from the supplier passing quality inspection.  I want to combine the measures I have successfully added to Supplier Names with Durability Risk to come up with a total Quality Risk on Supplier Names.  When I try to do:

 

Quality Risk =  [Measure1] + [Measure2] + [Durability Risk]

 

or

 

Quality Risk =  [Measure1] + [Measure2] + 'Supplier Names'[Durability Risk]

 

or 

 

Quality Risk =  [Measure1] + [Measure2] + RELATED(Suppliers[Durability Risk])

 

[Durability Risk] is out of context.  I can use:

 

Quality Risk =  [Measure1] + [Measure2] + SUM(Suppliers[Durability Risk])

v-xjiin-msft Super Contributor
Super Contributor

Re: Convert column to measure

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:

 

3.PNG

 

 

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.

jiwhite Regular Visitor
Regular Visitor

Re: Convert column to measure

@v-xjiin-msft,

 

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.