Parsing Multiple Measures From a String in DAX

[ruhor]

I have a DB table that contains water meter information for tiered billing:

meterid		name			graduated	mincharge	ranges			standardcalculation
------------------------------------------------------------------------------------------------------------------------------
9		Sunburg Water		TRUE		17.95		5000|7000|999999	C*.0025*+17.95|C*.005|C*.0062
10		Roseville Water		TRUE		7.65		17|84|999999		C*7.21|C*6.15|C*4.85
11		Roseville Sewer		TRUE		10.12		999999|			C*7.02|
22		Dayton Water		TRUE		13.49		999999|			C/1000*3.615|
27		Rochester Water		TRUE		0		7|160|2000|8000|999999	C/100*3.89|C/100*3.28|C/100*3.28|C/100*3.03|C/100*3.03

The billing ranges are delimited by the "pipe" character, I need to display these ranges on a dashboard. So for the first meter (sunberg Water) it would need to look something like this:

Consumption Ranges and Rates
From                            To                Rate calculation
0                                 5000              C*.0025*+17.95
5000                           7000              C*.005
7000                           999999          C*.0062

There are between 1 and 10 tiered values for various meters (some are sewer that I am ignoring), I wrote a little bit of DAX to parse out the first value and it works, but I am not sure I am going down the right track. Would the best strategy be to make 10 separate measures? Is it possible to write a loop to do this?

This is what I have so far:

lowerWaterTier =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'ruhor_views utilitymeters'[ranges] ),
        FIND ( "Water", 'ruhor_views utilitymeters'[name], 1, 0 )
    )
        <= 1,
    CALCULATE (
        LEFT (
            VALUES ( 'ruhor_views utilitymeters'[ranges] ),
            ( FIND ( "|", VALUES ( 'ruhor_views utilitymeters'[ranges] ), 1, 0 ) ) - 1
        ),
        FIND ( "Water", 'ruhor_views utilitymeters'[name], 1, 0 ) > 0
    )
)

Thanks!

[Greg_Deckler]

You wind up with this:

In looking at this, probably not exactly what you are looking for, but some form of this might get you there, will continue to take a look at it, I assume what you want is something like: 9 Sunburg Water True 17.95 C*.0025+17.95 5000 9 Sunburg Water True 17.95 C*.005 7000 9 Sunburg Water True 17.95 C*.0062 999999 ?

[ruhor]

Yes, looking at your table, I think that would get me to where I aim to go. I am new to the BI analytics field but have a software background. I am not familiar with the tools (ETL, splits/unpivots) required to implement the two solutions but will start looking in to them.

I would like to thank both of you for your help.

Thanks!

[Greg_Deckler]

@ruhor - A possible implementation along the lines of what @greggyb was suggesting. Required two splits and two unpivots. This was from using your sample data in an "Enter Data" query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDJCsIwEIZfRXKsMk7SZjurL+CCh9KDSlChKLQuCDl49y19EidpBavmkJlJ5v9myXNm2YDNzof1udr2lquTqyieTxcTMlyDlWQlInodLhsPPY0SQBQy6cccH8PWKMGKQc44Utr0WLvLvizdN1qDkrGCN9knVYPghFHAAy0DIxsY78Bm7trpE4ELsg3HtyAUPmpF+Bqvbqfj4WfAFDLbFQ45zZmkoKiBRq5j6c3O1aT9JoQhtecKvQj7Md0lBRixjPVvV5h/Lqat+7w/QsSK4gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [meterid = _t, name = _t, graduated = _t, mincharge = _t, ranges = _t, standardcalculation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"meterid", Int64.Type}, {"name", type text}, {"graduated", type logical}, {"mincharge", type number}, {"ranges", type text}, {"standardcalculation", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","ranges",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"ranges.1", "ranges.2", "ranges.3", "ranges.4", "ranges.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ranges.1", Int64.Type}, {"ranges.2", Int64.Type}, {"ranges.3", Int64.Type}, {"ranges.4", type text}, {"ranges.5", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","standardcalculation",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"standardcalculation.1", "standardcalculation.2", "standardcalculation.3", "standardcalculation.4", "standardcalculation.5"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"standardcalculation.1", type text}, {"standardcalculation.2", type text}, {"standardcalculation.3", type text}, {"standardcalculation.4", type text}, {"standardcalculation.5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"meterid", "name", "graduated", "mincharge", "ranges.1", "ranges.2", "ranges.3", "ranges.4", "ranges.5"}, "Attribute", "Value"),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Other Columns", {"meterid", "name", "graduated", "mincharge", "Attribute", "Value"}, "Attribute.1", "Value.1")
in
    #"Unpivoted Other Columns1"

[greggyb]

You'll want to solve this in ETL rather than as a measure. In Power Query you can split a field by a delimiter. The data structure that is likely best is to then pivot this split field so that you have a table with fields [MeterID], [Name], [Graduated], [MinCharge], [RangeID], [RangeMin], [RangeMax], where [RangeID] is a number 1-10 to identify the range in question for that [MeterID].

This table would have as many entries per [MeterID] as that meter has ranges.

You would depend on the default grouping that happens in any visual to handle the fact that you now have duplicates.

You might prefer to just have 10 columns for ranges (maybe 20 to have max/min for each range 1-10), but I would probably avoid that, myself.