Forum Discussion
Dax Logic Required
Hi All,
I have a transaction data having customer's transaction.
I am trying to solve the below requiremnt using dax. i need a measure 'CustomerIDCount'. need your help on ths.
requirement : i want the customerid count who has moved from status 'Appeal' to 'Shipment' for Type and SubType dimension depends on status date.
I am trying to solve the below requiremnt using dax. i need a measure 'CustomerIDCount'. need your help on ths.
requirement : i want the customerid count who has moved from status 'Appeal' to 'Shipment' for Type and SubType dimension depends on status date.
I was not able to find how to attach PBIX file so i am pasting the sample data below
transaction data:
| Key | CustomerID | Status | StatusDate | Type | SubType |
| 1 | 1 | Shipment | 15-03-2020 | Type1 | SubType1 |
| 2 | 1 | Appeal | 10-02-2020 | Type1 | SubType1 |
| 3 | 1 | Dispensed | 28-12-2019 | Type1 | SubType1 |
| 4 | 1 | Trial | 23-11-2019 | Type1 | SubType1 |
| 5 | 2 | Shipment | 11-03-2020 | Type3 | SubType3 |
| 6 | 2 | Appeal | 15-12-2019 | Type2 | SubType2 |
| 7 | 2 | Trial | 18-10-2019 | Type2 | SubType2 |
| 8 | 3 | Shipment | 15-03-2020 | Type3 | SubType3 |
| 9 | 3 | Appeal | 10-02-2020 | Type3 | SubType3 |
| 10 | 3 | Dispensed | 28-12-2019 | Type3 | SubType3 |
| 11 | 3 | Trial | 23-11-2019 | Type3 | SubType3 |
| 12 | 4 | Removed | 25-03-2020 | Type1 | SubType2 |
| 13 | 4 | Shipment | 21-01-2020 | Type1 | SubType2 |
| 14 | 4 | Appeal | 28-11-2019 | Type1 | SubType1 |
| 15 | 4 | Shipment | 18-10-2019 | Type1 | SubType1 |
output should show below data - i have two matrix report. it can be two different measures
Type CustomerIDCount
Type1 2
Type2 0
Type3 1
Type CustomerIDCount
Type1 2
Type2 0
Type3 1
SubType CustomerIDCount
SubType1 1
SubType2 0
SubType3 1
SubType1 1
SubType2 0
SubType3 1
Thanks,
Raj
hi Anonymous
You could use this two measure
Type CustomerIDCount = VAR _table = SUMMARIZE(FILTER('Table','Table'[Status]="Appeal"||'Table'[Status]="Shipment"),'Table'[CustomerID],'Table'[Type], "_value",IF(CALCULATE(MAX('Table'[StatusDate]),FILTER('Table','Table'[Status]="Shipment"))> CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))&&CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))<>BLANK(),1,0)) return SUMX(_table,[_value])SubType CustomerIDCount = VAR _table = SUMMARIZE(FILTER('Table','Table'[Status]="Appeal"||'Table'[Status]="Shipment"),'Table'[CustomerID],'Table'[SubType], "_value",IF(CALCULATE(MAX('Table'[StatusDate]),FILTER('Table','Table'[Status]="Shipment"))> CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))&&CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))<>BLANK(),1,0)) return SUMX(_table,[_value])Result:
here is sample pbix file, please try it.
Regards,
Lin
2 Replies
hi Anonymous
You could use this two measure
Type CustomerIDCount = VAR _table = SUMMARIZE(FILTER('Table','Table'[Status]="Appeal"||'Table'[Status]="Shipment"),'Table'[CustomerID],'Table'[Type], "_value",IF(CALCULATE(MAX('Table'[StatusDate]),FILTER('Table','Table'[Status]="Shipment"))> CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))&&CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))<>BLANK(),1,0)) return SUMX(_table,[_value])SubType CustomerIDCount = VAR _table = SUMMARIZE(FILTER('Table','Table'[Status]="Appeal"||'Table'[Status]="Shipment"),'Table'[CustomerID],'Table'[SubType], "_value",IF(CALCULATE(MAX('Table'[StatusDate]),FILTER('Table','Table'[Status]="Shipment"))> CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))&&CALCULATE(MIN('Table'[StatusDate]),FILTER('Table','Table'[Status]="Appeal"))<>BLANK(),1,0)) return SUMX(_table,[_value])Result:
here is sample pbix file, please try it.
Regards,
Lin