Forum Discussion

ruhor's avatar
ruhor
Frequent Visitor
10 years ago

Parsing Multiple Measures From a String in DAX

I have a DB table that contains water meter information for tiered billing:   meterid name graduated mincharge ranges standardcalculation ------------------------------------------------------...
Need Help
greggyb's avatar
greggyb
Resident Rockstar
10 years ago

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.