Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Narender
Resolver I
Resolver I

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

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

"

 

 

 

3 REPLIES 3
v-danhe-msft
Employee
Employee

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:

A.PNG

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

Now you could see the correct result.

D.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

1.jpg2.jpg3.jpg4.jpg

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.