Power Bi measure showing sum of value in place of count of vale

[Narender]

Hi All,

I have a query :

Select Count(ASSESSMENT.ASSESS_NO) NO_OF_ASSESSMENTS
From ASSESSMENT 
where ASSESSMENT.ENTRY_DATE Between '1-Jan-2015' And '31-Dec-2015'
and ASSESSMENT.ASSESS_NO In (Select ASSESSMENT.ASSESS_NEW_ASSESS_NO From ASSESSMENT NEW_ASSESSMENT)

Fo this i have writtren this DAX.

Measure 14 = CALCULATE(COUNT(ASSESSMENT[ASSESS_NO]),'NEW_ASSESSMENT'[Assesssment_New_Flag]="B")

I have created a relation betwenn ASSESS_NO and ASSESS_NEW_ASSESS_NO in power BI.

IN DAX i am calculating ASSESS_NO of ASSESSMENT WHERE  Assesssment_New_Flag =B of NEW_ASSESSMENT.

Assesssment_New_Flag is using as flag so that it will take only those record whose value present in NEW_ASSESSMENT.

It is showing data record count 50 . 

Measure 14 = CALCULATE(COUNT(ASSESSMENT[ASSESS_NO]),'NEW_ASSESSMENT'[Assesssment_New_Flag]="B")

In original it has to show 20 record count which is right count.

50 is possible when i calculate sum of assess number.

like 

Measure 14 = CALCULATE(SUM(ASSESSMENT[ASSESS_NO]),'NEW_ASSESSMENT'[Assesssment_New_Flag]="B")

Power Bi is doing sum of record but in actual it has to  count as per my count DAX.

Thanks,

Narender

"

[v-danhe-msft]

Hi @Narender,

Due to I could not reprodece your data stracture, I have entered some sample data and it could work on my side.

Sample data:

Create the measure:Measure = CALCULATE(COUNT(ASSESSMENT[ASSESS_NO]),'NEW_ASSESSMENT'[Assesssment_New_Flag]="B")

Now you could see the correct result.

If I misunderstood you, could you please offer me more information about your data structure or share your pbix file if possible?

Regards,

Daniel He

[Narender]

Hi Daniel,

Query:

Select Count(ASSESSMENT.ASSESS_NO) NO_OF_ASSESSMENTS From ASSESSMENT where ASSESSMENT.ENTRY_DATE Between '1-Jan-2015' And '31-Dec-2015' and ASSESSMENT.ASSESS_NO In (Select ASSESSMENT.ASSESS_NEW_ASSESS_NO From ASSESSMENT NEW_ASSESSMENT).

Above are the 4 screen shots ,

1) the replation ship between date_key & date     

 2) the replation ship access_no & assess_new_assess_no   

3) Measure & Assessment & Asessment 2 

4) Measure 14 DAX.

Assessment 2 table is copy of table Assessment .  In asssment 2, I have removed extra coumns and added 1 column "Assessment_New_Flag" and given value "B".

As per the query , I have added column "Assessment_New_Flag".

I am using this Assessment_New_Flag" in Measure 14.

Please let me know wheter i am doing right and why i am getting worng data .as i am counting the assess_no but it is showing sum of assess_no. 

Thanks,

Narender

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490