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.
Hi,
I have a database of knowledges for each employees.
There are some "defined profiles" based on levels for each knowledges.
I want to calculate the "grade" for each employee corresponding to each defined profiles (5* sum of knowledges for employees / sum of kowledges for the defined profile)
It's OK if I don't want to weight any knowledge :
Grade for AMC =5*( CALCULATE(SUM(DONNEES[Niveau])) / ( CALCULATE(SUM(DONNEES[Niveau]);all(Noms);Noms[trigramme]="AMC")
"AMC" corresponds to a defined profile
If I want to weight one knowledge, here #10, It's OK :
AMC grade weighted = ( CALCULATE(SUM(DONNEES[Niveau])) + 2*CALCULATE(SUM(DONNEES[Niveau]);DONNEES[compétence]=10)) / ( CALCULATE(SUM(DONNEES[Niveau]);all(Noms);Noms[trigramme]="AMC") +2*CALCULATE(SUM(DONNEES[Niveau]);DONNEES[compétence]=10;all(noms);Noms[trigramme]="AMC")) *5
But if I want to weight several knowledges, then I have to use the FILTER function, and the filter for my second term of the division does not work any more even with one knowledge applied :
AMC grade weighted with filter function = ( CALCULATE(SUM(DONNEES[Niveau]);LMC;DONNEES;METIERS;NOMS) + 2*CALCULATE(SUM(DONNEES[Niveau]);filter(DONNEES;DONNEES[compétence]=10))) / ( CALCULATE(SUM(DONNEES[Niveau]);all(Noms);Noms[trigramme]="AMC") +2*CALCULATE(SUM(DONNEES[Niveau]);filter(donnees;DONNEES[compétence]=10);all(noms);Noms[trigramme]="AMC")) *5
I don't see why my filter works for the first term but not for the 2nd!
Any advice, input?
Thanks
Solved! Go to Solution.
Hi @tvitte,
A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column. So you may need to use ALL function within the FILTER expression in your scenario like below.
AMC grade weighted with filter function = ( CALCULATE ( SUM ( DONNEES[Niveau] ); LMC; DONNEES; METIERS; NOMS ) + 2 * CALCULATE ( SUM ( DONNEES[Niveau] ); FILTER ( ALL ( DONNEES[compétence] ); DONNEES[compétence] = 10 ) ) ) / ( CALCULATE ( SUM ( DONNEES[Niveau] ); ALL ( Noms ); Noms[trigramme] = "AMC" ) + 2 * CALCULATE ( SUM ( DONNEES[Niveau] ); FILTER ( ALL ( DONNEES[compétence] ); DONNEES[compétence] = 10 ); ALL ( noms ); Noms[trigramme] = "AMC" ) ) * 5
Reference: http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx
Regards
Hi @tvitte,
A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column. So you may need to use ALL function within the FILTER expression in your scenario like below.
AMC grade weighted with filter function = ( CALCULATE ( SUM ( DONNEES[Niveau] ); LMC; DONNEES; METIERS; NOMS ) + 2 * CALCULATE ( SUM ( DONNEES[Niveau] ); FILTER ( ALL ( DONNEES[compétence] ); DONNEES[compétence] = 10 ) ) ) / ( CALCULATE ( SUM ( DONNEES[Niveau] ); ALL ( Noms ); Noms[trigramme] = "AMC" ) + 2 * CALCULATE ( SUM ( DONNEES[Niveau] ); FILTER ( ALL ( DONNEES[compétence] ); DONNEES[compétence] = 10 ); ALL ( noms ); Noms[trigramme] = "AMC" ) ) * 5
Reference: http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx
Regards
Thanks for your explanation. For the record I used the function switch to solve this problem before your answer.
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 |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |