Dax Logic Required

[RajeshLM]

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

SubType     CustomerIDCount
SubType1   1
SubType2   0
SubType3   1

Thanks,

Raj

[v-lili6-msft · Accepted solution]

hi  @RajeshLM 

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

[RajeshLM]

Thanks Lin. I will implement this logic in my model and let you know.