Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All ,
I have such below Table , Column of Purchase/No PUrchase ; Count are calculated in Edit Query , as of result this is the table :
Customer Code | Customer Name | Date | Value | Purchase/No Purchase | Count |
12345 | Abc | 1/1/2019 | 12 | Purchase | 1 |
12345 | Abc | 2/1/2019 | 23 | Purchase | 1 |
12345 | Abc | 3/1/2019 | 0 | No Purchase | 0 |
45678 | xyz | 1/1/2019 | 12 | Purchase | 1 |
45678 | xyz | 2/1/2019 | 23 | Purchase | 1 |
45678 | xyz | 3/1/2019 | 3 | Purchase | 1 |
Expected Result :
I wanna calculated which customer who re purchased and Non :
If Count >=3 , Repurchased ,
If Count < 3 , No Repurchased ,
I have calculated below columns :
Customer Code | Customer Name | Value | Purchase/No Purchase | Count | Repurchased/No Repurchased |
12345 | Abc | 35 | Purchased | 2 | No Repurchased |
45678 | xyz | 38 | Purchased | 3 | Re Purchased |
Solved! Go to Solution.
Please check below dax.
Count = CALCULATE ( COUNT ( 'Energy Drink'[Customer Name - Store host code] ), FILTER ( ALLSELECTED ( 'Energy Drink' [Customer Name - Store host code]), [Repurchased/No Repurchased]= SELECTEDVALUE('Re-Purchased/No Repurchased'[Type]) ) )
Regards,
Cherie
You may try to create a measure like below.If it is not your case,please show me the Master Outlet table.
RE-Purchased = IF ( SUM ( 'Energy Drink'[Count on Purchased] ) >= MAX ( 'Master Outlet'[Count Outlet] ), "Repurchased", "No Repurchased" )
Regards,
Cherie
@v-cherch-msft , hi the calculation works perfect , but can i have it in Column ? It will be easy to filter or slicer. of Repurchased or no Repurchased.
You may try below dax:
Column = IF ( CALCULATE ( SUM ( 'Energy Drink'[count] ), ALLEXCEPT ( 'Energy Drink', 'Energy Drink'[Customer Code ] ) ) >= RELATED ( 'Master Outlet'[Count Outlet] ), "Repurchased", "No Repurchased" )
Regards,
Cherie
Hi , when i use Date Filter , it dones't work , Measure Repurchased/No Repurchased , and Colum are different.
When i Filter "Date " , Measure- Repurchased/NoRepurchased filtered as expected , But Column - Column Calculation i used as per your mentioned was not run as date filter. you see it turns wrong .
how to solve such thing ? I want it to be filtered any slicer i used For example : Date
Only measure can be dynamic.If you create a column,it cannot be dynamic or changed by slicer.I would suggest you use measure instead of calculated column.
Regards,
Cherie
hi @v-cherch-msft ,if i acheived this by using Measure , i wanna count , How Many Re-purchased and No Repurchased . How to use the calculation ?
You may try create measure like below.If it is not your case,please share the .pbix file for us to test.You can upload the .pbix file to OneDrive and post the link here or send me via private message. Do mask sensitive data before uploading.
Repurchased Count = CALCULATE ( DISTINCTCOUNT ( 'Energy Drink'[Customer Code ] ), FILTER ( ALLSELECTED ( 'Energy Drink'[Customer Code ] ), [RE-Purchased1] = "Repurchased" ) )
Regards,
Cherie
@v-cherch-msft , https://www.dropbox.com/s/79j0c729sa3khda/20191203-Category%20Report%20V3.pbix?dl=0
hi , you can go and check the link above ,
*My purpose is also to see how many Repurchased and No Repurchased. it is all filtered based on the data filtering.
Please check below dax.
Count = CALCULATE ( COUNT ( 'Energy Drink'[Customer Name - Store host code] ), FILTER ( ALLSELECTED ( 'Energy Drink' [Customer Name - Store host code]), [Repurchased/No Repurchased]= SELECTEDVALUE('Re-Purchased/No Repurchased'[Type]) ) )
Regards,
Cherie
Hi @v-cherch-msft , I do the calculation for another Purchase/No Purchase , And i follow the calculation of Repurchased/No Repurchased as per your mentioned.
but it's strang , Purchase/No PUrchase Calculation : Count only 2 Customers Who Purchase , but for customer who get 0 it will go to No Purchased , but it doesn't count into No Purhcase , it keeps blank . therefore i can't see the number of customer who Purchase and No Purchase , Purchase is customer value >=1 , No Purchase <1 .
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |