How do I put a condition inside a filter in power BI (DAX)?

user_18056126 0
Viewed 1840

I am just getting started with Power BI and am struggling to create a measure. I am trying to count the sum of my column "Repartition", but with some filters.

Here is my current measure:

Nb_Actifs = CALCULATE (SUM(PAIE[Repartition]), FILTER(PAIE, PAIE[REMUNERABLE] = "Oui"), IF(PAIE[PROFIL]="CC", FILTER(PAIE, PAIE[Taux_Contractuel]<> 0)))

The filters that I put before my IF statement are the ones that I want to apply to all rows. The ones after the statement are the filters that I only want to apply to the rows where the condition PAIE[PROFIL]="CC" is met.

However, I get this error message: "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

From what I understand I need to replace the IF function by a filter, but since there are several filters already I get confused as to how to do that in Power BI.

Could someone help me?

dax powerbi
2 Answers

You can add some logic within your filter to catch all the conditions you need to return:

Nb_Actifs = 
    CALCULATE ( 
        SUM ( PAIE[Repartition] ), 
        FILTER ( 
            PAIE, 
            PAIE[REMUNERABLE] = "Oui" && (( PAIE[PROFIL] = "CC" && PAIE[Taux_Contractuel] <> 0 ) || PAIE[PROFIL] <> "CC" )
        )
    )
user_84665230

I think a SUMX function would be more appropriate here. You would also want to put all logic into a single FILTER. Try

Nb_Actifs = 
    SUMX(
        FILTER(
            PAIE, 
            PAIE[REMUNERABLE] = "Oui" && 
                (
                    (PAIE[PROFIL] = "CC" && PAIE[Taux_Contractuel] <> 0) || 
                    (PAIE[PROFIL] <> "CC")
                )
        ), 
        PAIE[Repartition]
    )

Regardless of whether you use SUMX or not, putting all of your logic into a single FILTER with corresponding logic would solve your conditional filter problem.

user_178844140
Related