DAX | How to iterate consecutive text modifications in a measure

[Alienvolm]

Hi, 

I'm struggling to find the right syntax for a measure that needs to produce a series of operations through a calculated column. 

I have column "Audio Degradation Average" that contains a STRING in this format: 015: [0.01 - 0.02), where: 

• 015 represents the average
• The values in the brakets represent the tier 

I need to: 

• Extract the first three characters representing the value (015 in the example) -> LEFT
• Transform 015 into a DOUBLE (ex: 0.15) -> CONVERT
• Calculate the total average of the column. -> AVERAGE

I started to create variables in my measure for each passage, but I cannot find the right aggregators or iterators to perform these operations row by row. I cannot use calculated columns as the dataset is already huge. 

Can anybody help? 

[Alienvolm]

Thanks, I guess it will be a learning curve... 

[daxer-almighty · Accepted solution]

@Alienvolm 

Looks like you have only one way out: you have to either write DAX in such a way that the engine is able to translate it into the language of the underlying source system (SQL, I guess), or you have to write the M code in exactly the same way. You just have to experiment and I would start with M as it has more functions.

[AlB]

@Alienvolm 

perhaps you can add that column we had earlier directly in the source? Or in the query editor?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Alienvolm]

Nope...

I also tried to add a calculated column = Audio Degradation Avg, with the intent of manipulating it later, but that's just not possible:

I don't have access to the data source... the only way is through the connector. 

Alienvolm

[AlB]

@Alienvolm 

So we'll do it all within the measure then: 

AVG measure  =
AVERAGEX (
    Table1,
    CONVERT ( LEFT ( Table1[Audio Degradation Average], 3 ), DOUBLE ) / 100
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Alienvolm]

Thanks! 

I was really hoping it would work: 

But this is what I get in the visuals: 

This seems to happen whenever I use SUMX or AVERAGEX in an expression, with or without CALCULATE. And I cannot work in Import mode as the dataset is too large. 

Thanks anyway, you definitely clarified the syntax for me! 

Alienvolm

[AlB]

Hi @Alienvolm 

How about you first create a calculated column in your table to get the number:

NewColumn =
CONVERT ( LEFT ( Table1[Audio Degradation Average], 3 ), DOUBLE ) / 100

and the create a measure that will calculate the average.

AVG Measure = 
AVERAGE ( Table1[NewColumn] )

Place the measure in a card visual

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Alienvolm]

Hi @AlB , 

That's my problem... I cannot create calculated columns. 

I'm working with the CQD connector in Direct Query, and calculated columns will not work:

Thanks! 

Alienvolm