Forum Discussion

NilR's avatar
NilR
Post Patron
5 years ago

Distinct Count doesn't match to Excel file raw data

I created Pivot in Excel and selected Distinct Count, however , the measure I created in PBI doesn't match to distinct count in Excel. Can you help me to fix my measure?

Excel: 

 

Measure:  

 

 

Var _Calc= CALCULATETABLE(
SUMMARIZE('COVID_INDV_PERIODS',[SERVICE_DESC],
"_COUNT", DISTINCTCOUNTNOBLANK('COVID_INDV_PERIODS'[ID])),
FILTER('COVID_INDV_PERIODS', [GP_NB]=_gpnb
&& [SERVICE_DESC]="Confirmed"
&& ([PAID_DATE] >= _min_Pdate && [PAID_DATE] <= _max_Pdate)
&& ([SERVICE_DATE] >= _min_SVCdate && [SERVICE_DATE] <= _max_SVCdate) ))

Return
SUMX(DISTINCT(_TempTable),[_COUNT])

 

 

 

 

Result in PBI which is wrong:

 

Thanks!

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    NilR Guessing but could be a casing problem. DAX ignores case so Greg and greg are 1 distinct value.

    • NilR's avatar
      NilR
      Post Patron
      5 years ago

      I checked, It's all Numbers ! Can I rewrite the measure in diff way? 

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi NilR ,

     

    Please provide a sample data and I will try to rebuild the measure in Power BI Desktop.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.