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

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
Kudo Commander
Kudo Commander

 

 

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

View solution in original post

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.

View solution in original post

Solution Sage
Solution Sage

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.

View solution in original post

9 REPLIES 9
Solution Sage
Solution Sage

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.

View solution in original post

@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.

Advocate II
Advocate II

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.

View solution in original post

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.

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

Kudo Commander
Kudo Commander

 

 

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

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors