OR statement in measure gives different value than summing two measures

[SBSTAE]

Hi,

I have the following measures:

1.  AantalMedewerkersRos =
   VAR _selDate = SELECTEDVALUE ('DatumTabel'[Date])
   RETURN CALCULATE(DISTINCTCOUNT(Merge2[personeelsnummer]);
   Merge2[dashHrEmploymenttypes.emptype] = 101 || Merge2[dashHrEmploymenttypes.emptype] = 111;
   Merge2[dashHrRosters.validfrom] <= _selDate;
   Merge2[dashHrRosters.validuntil] >= _selDate) 
2. Categorie101 =
   VAR _selDate = SELECTEDVALUE ('DatumTabel'[Date])
   RETURN CALCULATE(DISTINCTCOUNT(Merge2[personeelsnummer]);
   Merge2[dashHrEmploymenttypes.emptype] = 101;
   Merge2[dashHrRosters.validfrom] <= _selDate;
   Merge2[dashHrRosters.validuntil] >= _selDate)
3. Categorie111 =
   VAR _selDate = SELECTEDVALUE ('DatumTabel'[Date])
   RETURN CALCULATE(DISTINCTCOUNT(Merge2[personeelsnummer]);
   Merge2[dashHrEmploymenttypes.emptype] = 111;
   Merge2[dashHrRosters.validfrom] <= _selDate;
   Merge2[dashHrRosters.validuntil] >= _selDate)

This results in the following numbers:

What am I doing wrong? I want to make a pie chart with the categorie111 and categorie101 values. 

[v-lili6-msft · Accepted solution]

HI @SBSTAE 

This is a simple logic problem, see this sample Venn diagram picture, the area in the middle shows how the two overlap

Power bi desktop has 15 features, and power bi service has 15 features too.

But they have total features is 25, not 15+15=30.

AantalMedewerkersRos =CALCULATE(DISTINCTCOUNT([Feature]), [Type]="Power BI Desktop" ||  [Type]="Power BI Service")

CategoriePower BI Desktop =CALCULATE(DISTINCTCOUNT([Feature]), [Type]="Power BI Desktop")

CategoriePower BI Service =CALCULATE(DISTINCTCOUNT([Feature]), [Type]="Power BI Service")

Regards,

Lin

[amitchandak]

You are using DISTINCTCOUNT(Merge2[personeelsnummer]), there seem to be common values between two categories.

means one employee seems to have two dashHrEmploymenttypes.emptype

You can have concatenate field and have a distinct count or put values(dashHrEmploymenttypes.emptype) in you calculate .

VAR _selDate = SELECTEDVALUE ('DatumTabel'[Date])
RETURN CALCULATE(DISTINCTCOUNT(Merge2[personeelsnummer]);values(Merge2[dashHrEmploymenttypes.emptype]);
Merge2[dashHrEmploymenttypes.emptype] = 101 || Merge2[dashHrEmploymenttypes.emptype] = 111;
Merge2[dashHrRosters.validfrom] <= _selDate;
Merge2[dashHrRosters.validuntil] >= _selDate) 

Also, refer : https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin