Need DAX Query

Chinna Kanchireddy

2y1.1kPower BI

Extract unit Of Measure:

Example

diesel(KL)

water(mineral+waste)(KL)

wood(Tons)

Need to extract units of Measure like
KL
KL
Tons

Note: in water need to extarct only KL

Let me know if any one knows the logic

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi2y ago

Try this :

ExtractUnitOfMeasure = 
VAR StartPos = SEARCH("(", [YourColumn], 1, LEN([YourColumn]))
VAR EndPos = SEARCH(")", [YourColumn], StartPos, LEN([YourColumn]))
VAR UnitOfMeasure = MID([YourColumn], StartPos + 1, EndPos - StartPos - 1)
RETURN
TRIM(UnitOfMeasure)

Brahma Prakash Shukla2y ago
To extract the units of measure from your examples, you can use a combination of DAX functions like LEFT, RIGHT, FIND, and LEN. Assuming your data is in a column named "Product" in a table, here's a DAX query that can help you achieve this:
```
UnitOfMeasure = 
    SWITCH(
        TRUE(),
        CONTAINSSTRING([Product], "(KL)"), "KL",
        CONTAINSSTRING([Product], "(Tons)"), "Tons",
        // Add more conditions as needed for other units of measure
        
        // If none of the conditions match, return an empty string
        BLANK()
    )
```
This DAX query uses the CONTAINSSTRING function to check if the product description contains specific keywords like "(KL)" or "(Tons)". You can extend the conditions for other units of measure as needed.

Please replace [Product] with the actual column name in your table. If your data is in a different structure or format, you may need to adjust the logic accordingly.
+3