Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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
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.
@Anonymous,
Instead of calculated column in DAX, you could add column in Query Editor as well.
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!
@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] ) ) )
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |