Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Intersection Issue in DAX

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 ...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
6 years ago

Anonymous 

 

You can share your file with me if you like.

Are you using some VISUAL or PAGE level filters?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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:
ItemCustomerQTY
Item1cust14
Item1cust256
Item1cust378
Item2cust33
Item2cust430
Item2cust240
Item2cust567
Item3cust340
Item3cust234

 

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:

ItemCustomerQTY
Item1cust256
Item1cust378
Item2cust430
Item2cust240
Item2cust567
Item3cust340
Item3cust234

 

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!