Forum Discussion

qmartiny's avatar
qmartiny
Helper III
4 years ago
Solved

Distinct Count not working when exporting to excel

Hello lovely people.   I have a matrix visual with the format below   Master Account Sub Account Expected Distinct Count of Master AAA A_1 1 AAA A_2 0 BBB B_1 1 BBB B_2 0...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi qmartiny ,

     

    If you want the first row of each Master Account to show the distinct count and other rows show 0, you could create a formula as below.

    Column = 
var _min = CALCULATE(MIN('Table'[BP_ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
var _count = calculate(DISTINCTCOUNT('Table'[Top ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
return
IF('Table'[BP_ID]=_min,_count,0)

     

    Best Regard,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi qmartiny ,

 

If you want the first row of each Master Account to show the distinct count and other rows show 0, you could create a formula as below.

Column = 
var _min = CALCULATE(MIN('Table'[BP_ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
var _count = calculate(DISTINCTCOUNT('Table'[Top ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID])))
return
IF('Table'[BP_ID]=_min,_count,0)

 

Best Regard,

Jay