How to create reusable function

[Anonymous]

Hello,

I would like to create a reusable function and I cannot find a way to do that. Is it even possible? Here is my use-case. I have several columns e.g., "Score A" and "Score B" with scores that I want to map to a word. Thus, now for each column I have to create the following calculated column.

Qualitative score for A = if(X[Score A]<10,"Poor","Excellent")

Qualitative score for B = if(X[Score B]<10,"Poor","Excellent")

However if I need to change a word e.g., from "Poor" to "Not acceptable" I have to update each and every calculated column. Is there any way to create a DAX function, or any function somehow to calculate the qualitative score value of a score input?

[garythomannCoGC]

Custom (Reusable) DAX Function   please vote :}

[Anonymous · Accepted solution]

Eventually I created an M function following the tutorial here.

[v-chuncz-msft]

@Anonymous,

As mentioned above, you may select Unpivot Columns in the Query Editor and use Matrix visual later. To invoke a custom function, you could create a new Blank Query and add code below to Advanced Editor.

https://msdn.microsoft.com/en-us/library/mt185361.aspx

let
    QualitativeScore = (Score as number) => if Score < 10 then "Poor" else "Excellent"
in
    QualitativeScore

[Anonymous]

@v-chuncz-msft

unpivoting will not work because the columns are claculated columns. Invoking the custom function also works only on the edit query level thus no chance to do it if the score is a calculated column.

[v-chuncz-msft]

@Anonymous,

Instead of calculated column in DAX, you could add column in Query Editor as well.

[Anonymous]

@v-chuncz-msft

Yes you are right but DAX is much simpler than M when it comes to complex functions, even not so complex, simple if statements, or a SWITCH in M is alsmost impossible to do.

In my case the score that I want to calculate is a logarithm based on values of other columns. Anyways thanks for helping!

[v-chuncz-msft]

@Anonymous,

You may use the GENERATE Function similar to the CROSS APPLY condition in SQL.

Table =
GENERATE (
    X,
    UNION (
        ROW ( "Attribute", "Score A", "Value", X[Score A] ),
        ROW ( "Attribute", "Score B", "Value", X[Score B] )
    )
)

[Anonymous]

Hi @Anonymous,

Without knowing your table/scenario, I suppose that you want is something like that, but it will not work properly:

Qualitative score = if(X[Score A]<10 || X[Score B]<10 || X[Score C]<10 || X[Score D]<10 ,"Poor","Excellent")

... but, if each column have only one Score, I think that the best scenario is unpivot your table like this:

Atribute           |         Value
__________________________________
Score a            |           2
Score b            |          32
Score c            |          21
Score d            |          4

and create this column:

Qualitative Score = if(x[Value]<10 ,"Poor","Excellent")

[Anonymous]

@Anonymous

Thank youfor the answer. Each score should have its own qualitative value. The unpivot method could work but the columns are calculated columns so I cannot unpivot them since they do nto appear in the Query.