Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago
Solved

Customer Activity

Here I have table A it contains id, date, sector, zone. I created  a calculated column "Customer Relation"   if the customer has at least one transaction within the last 6 months then considers as a...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi manideep547 

    First, you should know that:

    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

    Second, for your case, you could try this way:

    Step1:

    you need to use two separate tables of date and Customer Activity state for slicer

    Step2:

    Create a measure that show Customer Activity state for each id dynamically

    customer Relation = IF(DATEDIFF(SELECTEDVALUE('Table A'[date]) ,MIN('Date'[Date]),DAY)>6,"Inactive","Active")

     NOTE: you could just replace "DAY" with "MONTH" for this formula, I just use it as a simple sample.

    Step3:

    Then create a measure that count id that based on Customer Activity state

    Result = 
var _table=ADDCOLUMNS('Table A',"_customer Relation",[customer Relation]) return
COUNTAX(FILTER(_table,[_customer Relation]=SELECTEDVALUE('Customer Activity'[State])),[id])

    Result:

     

    and here is a sample pbix file, please try it.

     

    Regards,

    Lin

SivaMani's avatar
SivaMani
Resident Rockstar
6 years ago

manideep547,

 

Assuming your slicer use this field - Date[Cal Date].

Try the below calculation,

customer Relation=  if(datediff(Table A[Date],MIN(Date[Cal Date]),MONTH)>6,"Inactive","Active")
 
Hope it will help you!
Thanks,
Siva Mani
  • manideep547's avatar
    manideep547
    Helper III
    6 years ago

    After selecting the date in the slicer. If I click on active it will show active customers(another visualizer should be change based on active ) and if I click on the inactive then it will show the inactive customers(another visualizer should be change based on active)

     

    After the selection of the date, we click the active and inactive. based on that other cards should be changed. For direct query@sivaMani

     

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      6 years ago

      hi manideep547 

      First, you should know that:

      1. Calculation column/table not support dynamic changed based on filter or slicer.
      2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

      https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

      Second, for your case, you could try this way:

      Step1:

      you need to use two separate tables of date and Customer Activity state for slicer

      Step2:

      Create a measure that show Customer Activity state for each id dynamically

      customer Relation = IF(DATEDIFF(SELECTEDVALUE('Table A'[date]) ,MIN('Date'[Date]),DAY)>6,"Inactive","Active")

       NOTE: you could just replace "DAY" with "MONTH" for this formula, I just use it as a simple sample.

      Step3:

      Then create a measure that count id that based on Customer Activity state

      Result = 
var _table=ADDCOLUMNS('Table A',"_customer Relation",[customer Relation]) return
COUNTAX(FILTER(_table,[_customer Relation]=SELECTEDVALUE('Customer Activity'[State])),[id])

      Result:

       

      and here is a sample pbix file, please try it.

       

      Regards,

      Lin

      • manideep547's avatar
        manideep547
        Helper III
        6 years ago

         

        Here I changed day to months and in slicer also I changed the date but it showing the wrong functionality(Active) min date is 1/1/2009 and table date is 1/1/2019 it is showing active