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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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'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!