How to distinct count based on 2 different columns

[o59393]

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.

[mahoneypat · Accepted solution]

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 · Accepted solution]

@o59393 

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

[Greg_Deckler · Accepted solution]

@o59393 

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

[o59393]

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!

[mahoneypat · Accepted solution]

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

[o59393]

Correct @mahoneypat !

I added that and worked.

Thank you both @Greg_Deckler and @mahoneypat 

[Greg_Deckler]

@mahoneypat - Nice one, I totally missed that he was wanting this to be dynamic or have an additional filter on it.

[Greg_Deckler]

@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

[mahoneypat · Accepted solution]

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