Forum Discussion

DPIT_FR's avatar
DPIT_FR
Regular Visitor
2 years ago

combine filter doesn't work (with parameters)

Hello all,

I tried to filter a table with two filters, but it doesn't work (returns blank lines except for a curious total).

My objective is to enable users to filter based on a threshold (with parameter) and calculate it for the previous year. it's ok for the current year, but not for Y-1


I have two measures that work individually:

First: (filter line)

 
Nb Passage Horaire (Compteurs EXCLUS) =
CALCULATE(sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),
    FILTER(
        Passage,
        Passage[NB_PASSAGE_HORAIRE_ESTIME] < 'ParamètreExclusion'[ParamCapteurDouteuxRetour]
    )
)


Second : (filtre Y-1)

Nb Passages Horaire (A-1) =
CALCULATE(
    sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),
    Dateadd(Calendrier[Date],-1,YEAR)
)

 

However, when I try to combine both, it doesn't work. I've tried various approaches, including TREATAS, but nothing seems to work 😔.

Here is one combined measure i tried


Nb Passage Horaire A-1 (Compteurs EXCLUS) =
CALCULATE(
SUM(Passage[NB_PASSAGE_HORAIRE_ESTIME]),
DATEADD(Calendrier[Date], -1, YEAR),
FILTER(
Passage,
Passage[NB_PASSAGE_HORAIRE_ESTIME] < 'ParamètreExclusion'[ParamCapteurDouteuxRetour]
)
)


if you can explain it...

Thanks a lot

Need Help

4 Replies

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello DPIT_FR  I would like to help you could you please share with me a power bi file with fake / anonymize data ? send it to me in private 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi freginier ,Thanks for the quick reply, I'll add further.

    Hi DPIT_FR ,

    The Table data is shown below:

     

    Regarding your question, please modify your measure according to the expression I provided.

    MEASURE =
CALCULATE (
    SUMX (
        FILTER ( 'Table', 'Table'[Value] > 'Parameter'[Parameter Value] ),
        [Value]
    ),
    DATEADD ( 'Calendar'[Date], -1, YEAR )
)

    Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • DPIT_FR's avatar
    DPIT_FR
    Regular Visitor
    2 years ago

    hello freginier , Anonymous 

     

    Thanks a lot for your help. Unfortunately, I can't get the result... and I don't understand how I get this result....

    Unfortunately, I wasted too much time on this problem, I will  see with my client if it is not possible to remove this functionality...

    However, if you see another possibility, I will take note of it. (I put the formulas below). In any case, once again I thank you for the time spent.

     

     

    Nb Passage Horaire (Compteurs EXCLUS) =
    CALCULATE(sum(Passage[NB_PASSAGE_HORAIRE_ESTIME]),
        FILTER(
            Passage,
            Passage[NB_PASSAGE_HORAIRE_ESTIME] < parameter[Parameter_value]
        )
    )
     
    TestYearBefore1 =
    CALCULATE(
       SUMX(         FILTER(         Passage,         Passage[NB_PASSAGE_HORAIRE_ESTIME] < parameter[Parameter_value] && DATEADD(Calendrier[Date], -1, YEAR)         ),Passage[NB_PASSAGE_HORAIRE_ESTIME]     ) )
     
     
    TEstYearBefore2 =
    CALCULATE (
        SUMX (
            FILTER ( 'Passage', 'Passage'[NB_PASSAGE_HORAIRE_ESTIME] > parameter[Parameter_value] ),
            [NB_PASSAGE_HORAIRE_ESTIME]
        ),
        DATEADD ( 'Calendrier'[Date], -1, YEAR )
    )
     
    one more time :thank you much