Filter by measure

[Anonymous]

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

[Anonymous]

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]

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]

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.

[Anonymous]

Hi @Anonymous ,

If you want to put the measure2 in the table visual that shows 163 for each row, you could modify the measure2 as below.

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

Measure1 is work for measure2, you can remove it from the visual and use A-(A-1) instead.

Best Regards,

Jay

[Anonymous]

I do not achieve the same result as you in my case. Probably in my A-measure I use time intelligence. I don't know. Here is my pbix shared from my drive, can you look at it?

https://drive.google.com/drive/folders/1Akd6m-BNNRdCtau64eBQ3swdlhq6vbzF?usp=sharing

[Anonymous · Accepted solution]

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]))))

[Anonymous]

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