Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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.
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])
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])
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))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |