How to distinct count based on 2 different columns

Hi all

How can I do a distinct count for the column "month 445" where it depends on the column "bottler"

In the image above, the result desired for bottler "AB" should be 7, since for that "AB" bottler there are 7 months of value.

For "FCR" the result desired is 4.

How can I get right my calculated column called "distinct count"

PBI: https://1drv.ms/u/s!ApgeWwGTKtFdhydAQ7n3Th2KpUHl?e=GK3MCU

Thanks.

Need Help

Show and Tell

mahoneypat
6 years ago
This should probably be done as a measure, but here is a column expression you can use.

NewColumn = CALCULATE(DISTINCTCOUNT(Sheet1[Month 445]), ALLEXCEPT(Sheet1, Sheet1[L1.2 - Bottler]))

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Greg_Deckler

6 years ago

o59393

Column =
  VAR __Table = SELECTCOLUMNS(FILTER('Table',[L1.2 - Bottler]=EARLIER([L1.2 - Bottler])),"Month",[Month 445])
RETURN
  COUNTROWS(DISTINCT(__Table))

mahoneypat
6 years ago
I see the issue. Columns do not recalculate or respond to filters (either in data view or slicers in visual). If you want it to consider the code too, you can adapt the code to this to get a result of 7. Otherwise you need to make a measure instead and use it in your visuals.

NewColumn =
CALCULATE (
DISTINCTCOUNT ( 'Import de capacity'[[Month 445]]] ),
ALLEXCEPT (
'Import de capacity',
'Import de capacity'[[L1.2 - Bottler]]],
'Import de capacity'[[ BPP Code ]]]
)
)

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

7 Replies

mahoneypat
Microsoft Employee
6 years ago
This should probably be done as a measure, but here is a column expression you can use.

NewColumn = CALCULATE(DISTINCTCOUNT(Sheet1[Month 445]), ALLEXCEPT(Sheet1, Sheet1[L1.2 - Bottler]))

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat
Greg_Deckler
Community Champion
6 years ago
o59393

Column = VAR __Table = SELECTCOLUMNS(FILTER('Table',[L1.2 - Bottler]=EARLIER([L1.2 - Bottler])),"Month",[Month 445]) RETURN COUNTROWS(DISTINCT(__Table))
- o59393
  Post Prodigy
  6 years ago
  Hi Greg_Deckler and mahoneypat
  
  Both worked on the test pbix.
  
  When I go to my real pbi and use both formulas I get an "8" again.
  
  You may filter by code 232031405 and bottler "abasa" and "femsa cr" to see the example of the test pbix and image above.
  
  Any idea what could be wrong?
  
  Thanks!
  - Greg_Deckler
    Community Champion
    6 years ago
    o59393 - It's because there really are 8 distinct values of Month 445. Filter L1.2 - Bottler to Coca-Cola Femsa CO, both the columns display 8
    
    That's because there are 8 distinct values, count them:
    
    8