Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter by measure

Hello,

I am looking for a way to count the number of distinct lines following a measurement,

An annual data set is retrieved in the same table allowing to make measurements vs previous years,

Annual dataset : Year / Leave / Taken / Remaining to be taken
Measure "acquired rest" = Remaining to take A + Taken A - Remaining to take A-1
Following this measure, I want to count the number of agents concerned, i.e. only those who have acquired rest during the year,

I already use DISTINCTCOUNT to know the number of agents who have a rest counter but I want when it must be filtered by a measure, an idea via a formula, an intermediate table, etc..

Thanks in advance

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I find the solution

    Pris + Reste P A = CALCULATE(SUM(COMPTEUR_RS[Repos Compensateur Pris [jours]]])+SUM(COMPTEUR_RS[Repos Compensateur Reste à prendre [jours]]]);FILTER(COMPTEUR_RS;YEAR(COMPTEUR_RS[Période])=SELECTEDVALUE('DATE'[Année])))
     
     
    Total positifs = SUMX(VALUES(COMPTEUR_RS[Agent ID]);IF(CALCULATE(SUMX(COMPTEUR_RS;[Pris + Reste P A])-SUMX(COMPTEUR_RS;[Pris + Reste P A-1]))<0;0;CALCULATE(SUMX(COMPTEUR_RS;[Pris + Reste P A])-SUMX(COMPTEUR_RS;[Pris + Reste P A-1]))))
     
     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Not very clear. Are you want to add the measure to the distinct count calculation?

    Such as:

    calculate(distinctcount(value),filter(table,condition= measure))

    If I misunderstand your meaning, please show some sample data and expected result.

     

    Best Regards,

    Jay

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your answer Anonymous . I am doing a simple addition followed by a subtraction.

    Some results are negative so I filter them with my > 0.

    In my table row by row it is displayed correctly. However in a visual where I want to calculate the total sum of positive values, I get 0. See the screen.

     

     

     

    A - A-1 FILTER = IF([A - A-1]>0;[A - A-1];0)
     
     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

       

      You could use sumx(0 function.

      Refer:

      Measure = IF([A]-[A-1]>0,[A]-[A-1],0)

Measure 2 = SUMX('Table',[Measure])

       

      Best Regards,

      Jay

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Thanks for the measurement but it gives me the same result as the year A measurement. 

        A measurement. And do not filter me any more the negative values that I do not count any more.