Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RajeshLM
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
v-lili6-msft
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
v-lili6-msft
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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.