Intersection Issue in DAX

[Anonymous]

Hi All,

Since i didnt get proper solution in last post. I m posting another time.

I have two columns with name Product Code and Customer Name. I want to calculate the unqiue number of customers who bought common Products. For easy going, i m giving sample data here:

ProductCode	CustomerCode
A1001	C/001
A1001	C/002
B1004	C/001
B1004	C/004
B1004	C/003
C1005	C/001

I want following Output:

I drag ProductCode in slicer and select only A1001, then distinct count of customer code should be calculated who bought this Product.

My Output should be 2

Now suppose i multiselect A1001 and B1004 and i should get 1 in output as there is only customer who bought both products.

I also want to see the list of customer who bought both products only or depending upon if i multiselect 3 or 4 Product code.

Basically list of only unique Customers who bought products Based on AND.

Any Help would be appreciated

Thanks in advance

[Zubair_Muhammad]

@Anonymous 

Similarly if you want the list of such customers in a card visual, you can use this MEASURE

ListOfCustomers =
VAR myproducts =
    COUNTROWS ( ALLSELECTED ( Table1[ProductCode] ) )
RETURN
    CONCATENATEX (
        FILTER (
            VALUES ( Table1[CustomerCode] ),
            COUNTROWS (
                INTERSECT (
                    ALLSELECTED ( Table1[ProductCode] ),
                    CALCULATETABLE ( VALUES ( Table1[ProductCode] ) )
                )
            ) = myproducts
        ),
        [CustomerCode],
        UNICHAR ( 10 )
    )

[Anonymous]

Hi,

@Zubair_Muhammad 

I want the list of customers in the Grid only as i have more than 10,000 customers.

how do i go for it??

[Zubair_Muhammad]

@Anonymous 

We can do it with another MEASURE and use it as a VISUAL filter.

But is my formula working now?

[Anonymous]

Hi,

@Zubair_Muhammad 

Your Formula for Intersection is still not working in my enviorment, though i have calculated some logics in SQL and import that logic as a column and then used my Previous DAX which i have shared and its working. But i m not able to get the Names of Customers based on my DAX. Can you please help me into this??

[Zubair_Muhammad]

@Anonymous 

If you can share your file, I will check if I can help

[Zubair_Muhammad]

@Anonymous 

Try this MEASURE

Measure =
VAR myproducts =
    COUNTROWS ( ALLSELECTED ( Table1[ProductCode] ) )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Table1[CustomerCode] ),
            COUNTROWS (
                INTERSECT (
                    ALLSELECTED ( Table1[ProductCode] ),
                    CALCULATETABLE ( VALUES ( Table1[ProductCode] ) )
                )
            ) = myproducts
        )
    )

[Anonymous]

HI @Zubair_Muhammad 

I' m getting values by selecting individual ProductCode but getting Blank values if i do Multiselect on ProductCode.

Can you suggest for this one?

[Zubair_Muhammad]

Hi @Anonymous 

I am attaching the pbix file with your sample data

I get correct figures as you mentioned.

Please take a look and let me know

[Anonymous]

Hi,

@Zubair_Muhammad 

Still not working  in my pbix file. 

Momemt i multi-select itemcode, it gives me blank values. However it is working in yours. I m not able to understand .

Please suggest something??

[Zubair_Muhammad]

@Anonymous 

You can share your file with me if you like.

Are you using some VISUAL or PAGE level filters?

[Anonymous]

Hi,

@Zubair_Muhammad 

Yes i m using multiple page level filters. Though following DAX is helping in case of Intersection.

But not able to filtered out on DAX Level.

IF (

ISBLANK (

CALCULATE(COUNTROWS (

FILTER (

SUMMARIZE (

Cust_table,

Cust_table[CUST_CODE],

"ProductsBought", DISTINCTCOUNT ( Sales[SKU])

),

[ProductsBought] = COUNTROWS ( VALUES ( Sales[SKU] ) )

))

))

,

0,

CALCULATE(COUNTROWS (

FILTER (

SUMMARIZE (

Cust_table,

Cust_table[CUST_CODE],

"ProductsBought",DISTINCTCOUNT ( Sales[SKU])

),

[ProductsBought] = COUNTROWS ( VALUES ( Sales[SKU] ) )

)

)))

But this gives me result in general, following example will be easy to understand.

I have 3 Items and suppose 100 customers bought each item. It means i have 300 Customers in total (item Wise).

Now by using above DAX i m getting 50 Common Customer who bought all three (if i do multiselect on these 3 items).

But i have a measure of Average quantity also. so if average quantity is greater than 10 then it will included in a list of Customers (what i get in begining is 300, but suppose i will get 250 only while applying this filter) and then only calculate Intersection (AND) dax over this filteres customers.

I m not able to do this.

Suppose:

This is my sample Sales Data:

Item	Customer	QTY
Item1	cust1	4
Item1	cust2	56
Item1	cust3	78
Item2	cust3	3
Item2	cust4	30
Item2	cust2	40
Item2	cust5	67
Item3	cust3	40
Item3	cust2	34

What i m getting is count of 2 customers if i do multiselect on Item1 and Item2 and Item3.

But what i want to consider average quantity measure first (that means only consider those customer list where average quantity is greater than 10) which would be:

Item	Customer	QTY
Item1	cust2	56
Item1	cust3	78
Item2	cust4	30
Item2	cust2	40
Item2	cust5	67
Item3	cust3	40
Item3	cust2	34

Now if i apply same DAX over here i will get output of 1 customer who bought item1 and Item2 and Item2, which is accurate 

I do not want to apply filter after applying DAX of intersection, rather before doing so i prefer to identify customers based on measure.

Please help!