Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
"
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |