Countrows combined with DISTINCT and FILTER

[RemiAnthonise]

Hi guys, I have the following measure:

Range 1 = (CALCULATE(COUNTROWS(WorkerPositionEarningCodes); FILTER(WorkerPositionEarningCodes; [Range kostprijs] = 1)))
I have the same for range 2 & range 3, where the ranges are as following:

Range kostprijs =
IF([Salaris] <> BLANK();
IF([Kostprijs per uur] >= 0 && [Kostprijs per uur] <= 24,99; 1;
IF([Kostprijs per uur] >= 25 && [Kostprijs per uur] <= 44,99; 2;
IF([Kostprijs per uur] >= 45; 3))))

This isn't too hard.

I don't exactly know why but this gives me the following data. See the results per each range: it gives me '2' or '3' as a value. Like I said: I don't exactly know why. I think it has something to do with the countrows. 

What I would like to see is a distinctcount by Name of PersonnelNumber, so that Range 1 gives a total of 3, range 2 of 2 and range 3 of 2.

I've tried it with an 'IF': 
Range 1 = IF(CALCULATE(COUNTROWS(WorkerPositionEarningCodes); FILTER(WorkerPositionEarningCodes; [Range kostprijs] = 1)) > 0; 1; (CALCULATE(COUNTROWS(WorkerPositionEarningCodes); FILTER(WorkerPositionEarningCodes; [Range kostprijs] = 1))))

This gives me 1 as a result where I expect 3.

What do I miss?

[v-piga-msft]

Hi  @RemiAnthonise,

I have made a test with your data and your formula. It seems that I could get the output you want as below.

The only difference with your formula is that I have not reference [Salaris].

If my output is your desired, you could have a reference of this attachment.

If you need additional help, please share your data sample and expected output.

Best  Regards,

Cherry

[RemiAnthonise]

Hi @v-piga-msft ,

I found out what causes my problem. It's possible our employees get mutations on their salary. For example, someone earns 2000 euro a month and he earns a raise to 2500 euro. I have a valid from / valid to column for each row. In my previous table, it counts all the rows. See the image below.

I've tried to solve this by giving the range 1 / range 2 / range 3 a MAX for ValidFrom, like this:

Range 1 = (CALCULATE(COUNTROWS(WorkerPositionEarningCodes); FILTER(WorkerPositionEarningCodes; [Range costprice] = 1 && WorkerPositionEarningCodes[ValidFrom]=MAX(WorkerPositionEarningCodes[ValidFrom]))))

This gives me the following result (and I don't know why):


As you can see the results in my table are alright except the totals for each column. I would to represent it in a chart.
Do you have any clue? 

Thanks,
Remi

[v-piga-msft]

Hi @RemiAnthonise,

---

---

Range 1 = (CALCULATE(COUNTROWS(WorkerPositionEarningCodes); FILTER(WorkerPositionEarningCodes; [Range costprice] = 1 && WorkerPositionEarningCodes[ValidFrom]=MAX(WorkerPositionEarningCodes[ValidFrom]))))

This gives me the following result (and I don't know why):

The result is caused by the filter condition you highlight in red. The MAX(WorkerPositionEarningCodes[ValidFrom] is for [Range costprice] =2, so your Range 2 have the countrows value 1 and your others to be blank.

Are you sure you need the filter condition WorkerPositionEarningCodes[ValidFrom]?

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[RemiAnthonise]

Hi @v-piga-msft ,

Can you open my .pbix with this url?

Thank you!

[v-piga-msft]

Hi @RemiAnthonise,

Sorry, I cannot get your data through the URL. You upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[RemiAnthonise]

Hi Cherry @v-piga-msft ,

This should work. I'm sorry for the inconvenience.

[v-piga-msft · Accepted solution]

Hi @RemiAnthonise,

Here is should be your desired output.

I have create three new measure, measure1, measure2, measure3. You could see the details in the below attachment.

Best  Regards,

Cherry

[RemiAnthonise]

Hi Cherry @v-piga-msft ,

Thanks!