Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tvitte
Regular Visitor

Using two filters with CALCULATE

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

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.