topic Re: get counts based on nested conditions in DAX Commands and Tips

get counts based on nested conditions

Anonymous — Mon, 17 Jun 2024 20:17:10 GMT

I have following table

Product	Customer number
A	1
B	1
C	1
F	2
A	2

Definition of customer: If the customer has purchased product A or B, only then we consider the customer.

I want to create a count of customers based on these criteria. Also, it needs to add slicer products. However, whenever I select product F, technically it should show 1 customer. But it is showing blank.

Re: get counts based on nested conditions

ryan_mayu — Tue, 18 Jun 2024 00:54:07 GMT

Anonymous

maybe you can try to create a new column

count =

VAR _A=maxx(FILTER('Table','Table'[Customer number]=EARLIER('Table'[Customer number])&&'Table'[Product]="A"),'Table'[Product])

VAR _B=maxx(FILTER('Table','Table'[Customer number]=EARLIER('Table'[Customer number])&&'Table'[Product]="B"),'Table'[Product])

RETURN IF(_A<>""&&_B<>"",1,0)

then you just need to sum the count column

Re: get counts based on nested conditions

Anonymous — Tue, 18 Jun 2024 02:53:22 GMT

Thank you ryan_mayu

Hi, Anonymous

Based on the data you provided and the expected results described, I created a measure using the following DAX expression:

Count Customer = VAR _seleted_product = SELECTCOLUMNS ( 'Table', 'Table'[Product] ) VAR _table = FILTER ( 'Table', 'Table'[Product] IN _seleted_product ) VAR _ID_AB = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Product] IN { "A", "B" } ), 'Table'[Customer number] ) RETURN IF ( ISFILTERED ( 'Table'[Product] ), COUNTROWS ( FILTER ( _table, 'Table'[Customer number] IN _ID_AB ) ) )

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

Re: get counts based on nested conditions

Anonymous — Tue, 18 Jun 2024 12:11:24 GMT

This is good. However, if I select A, B and F, it should return 2 as there are only two unique customer

Re: get counts based on nested conditions

Anonymous — Mon, 24 Jun 2024 06:12:11 GMT

Hi, Anonymous

Thank you very much for your reply. Using the following DAX expression will get a count of different IDs:

Count Customer = VAR _seleted_product = SELECTCOLUMNS ( 'Table', 'Table'[Product] ) VAR _table = FILTER ( 'Table', 'Table'[Product] IN _seleted_product ) VAR _ID_AB = SUMMARIZE ( FILTER ( ALL ( 'Table' ), 'Table'[Product] IN { "A", "B" } ), 'Table'[Customer number] ) RETURN IF ( ISFILTERED ( 'Table'[Product] ), COUNTAX ( SUMMARIZE ( FILTER ( _table, 'Table'[Customer number] IN _ID_AB ), 'Table'[Customer number] ), 'Table'[Customer number] ) )

Here are the results:

I've uploaded the PBIX file below for your reference.

Best Regards

Jianpeng Li

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