Forum Discussion

jiwhite's avatar
jiwhite
Advocate I
8 years ago
Solved

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 follo...
Need Help
  • BraneyBI's avatar
    BraneyBI
    8 years ago

     

     

    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's avatar
    jiwhite
    8 years ago

    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's avatar
    v-xjiin-msft
    8 years ago

    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.

jiwhite's avatar
jiwhite
Advocate I
8 years ago

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's avatar
BraneyBI
Kudo Commander
8 years ago

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's avatar
    jiwhite
    Advocate I
    8 years ago

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