Filtered Column Before applying DAX

[Anonymous]

Hi All,

I have a table consisting Customer code, product code and multiple calculated measures and columns. I have calculated "AND/Intersection" logic to calculate how many customer bought items in together. 

For example- If i multselect 4 items then  my DAX is Giving me how many customers bought 4 items in together.

MY DAX:

Intersection of Items =
IF (ISBLANK (CALCULATE(COUNTROWS (FILTER (SUMMARIZE (BI_Cust_Data_Table,BI_Cust_Data_Table[CUST_CODE],
"ProductsBought",DISTINCTCOUNTNOBLANK(BI_SALESDATA[CUST_CODE])),
[ProductsBought] = COUNTROWS ( VALUES ( BI_SALESDATA[SKU] ) )
))
)), 0,
CALCULATE(COUNTROWS (FILTER (SUMMARIZE (BI_Cust_Data_Table,BI_Cust_Data_Table[CUST_CODE],
"ProductsBought",DISTINCTCOUNTNOBLANK(BI_SALESDATA[CUST_CODE])),
[ProductsBought] = COUNTROWS ( VALUES ( BI_SALESDATA[SKU] ) )
)
)))

But the problem is i have quantity in a measure and first my tast is to identify all customers (item wise) who bought quantity 1 or greather than one only. This should be item wise. Like for item 001, we have 100 customers and out of 100 customers only 97 have quantity greater equal to 1.

For this i have creater a new measure called Filtered Intersection:

Filtered Intersection = CALCULATE(DISTINCOUNT(CUST_CODE), FILTER(bi_cust_table, [ACTUALQUANTITY]>=1))

Basically, i m getting accurate value by using 'Intersection of Items' dax,but i m not getting accurate value while using 'Filtered Intersection' DAX.

I guess, I am not able to filtered out customer code first based on Qty>=1 and then use 'Intersection of Items' dax.

Any help would be appreciated

I can give more information, if required.

[v-shex-msft]

HI @Anonymous ,

Current power bi not support to create dynamic calculated column/table based on filter/slicer.
You can write dynamic measures based on filters effect but their results will been fixed as static values when you use them in calculated column.

It's like to suggest you use measure formula to calculate dynamic result.

Intersection of Items =
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( BI_Cust_Data_Table ), [ACTUALQUANTITY] >= 1 ),
        BI_Cust_Data_Table[CUST_CODE],
        "ProductsBought", DISTINCTCOUNTNOBLANK ( BI_SALESDATA[CUST_CODE] )
    )
VAR _count =
    COUNTROWS ( VALUES ( BI_SALESDATA[SKU] ) )
RETURN
    COUNTROWS ( FILTER ( summary, [ProductsBought] = _count ) ) + 0

Regards,

Xiaoxin Sheng

[Anonymous]

HI,

@v-shex-msft 

Basically i have created columns based on filter in SQL only and then just implement interesection Dax without any filter and now its working.

But what if i want to get the list of customers who bought common items.

In other words, can i have a list of customers based on your DAX??

[v-shex-msft]

Hi @Anonymous ,

My formula is merge your filter condition with summarize table and get corresponding user count.

>>But what if i want to get the list of customers who bought common items.
You can write measure to get filtered records and apply on visual level filter to compare and filter records. Can you please share some detail information to help us clarify your requirement? (e.g. expected result, table data structure) 

Regards,

Xiaoxin Sheng