Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtered Column Before applying DAX

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

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

HI,

Anonymous 

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??

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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