cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 was not able to find how to attach PBIX file so i am pasting the sample data below
 
transaction data:
KeyCustomerIDStatusStatusDateTypeSubType
11Shipment15-03-2020Type1SubType1
21Appeal10-02-2020Type1SubType1
31Dispensed28-12-2019Type1SubType1
41Trial23-11-2019Type1SubType1
52Shipment11-03-2020Type3SubType3
62Appeal15-12-2019Type2SubType2
72Trial18-10-2019Type2SubType2
83Shipment15-03-2020Type3SubType3
93Appeal10-02-2020Type3SubType3
103Dispensed28-12-2019Type3SubType3
113Trial23-11-2019Type3SubType3
124Removed25-03-2020Type1SubType2
134Shipment21-01-2020Type1SubType2
144Appeal28-11-2019Type1SubType1
154Shipment18-10-2019Type1SubType1
 
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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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:

5.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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:

5.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors