Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |