Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jiwhite
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
BraneyBI
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

v-xjiin-msft
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
v-xjiin-msft
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.

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

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.