DAX - Get distinctcount of locations based on last visit and joined again with table for filter

kbarilar — Mon, 22 Apr 2024 17:27:33 GMT

Data in table TableDist1 are:

ID CustomerID DistDate UserID ProductID isPresent

36094 CI2995 2024-01-25 P111 101 1.00000

33027 CI2995 2024-01-11 P111 101 0.00000

37326 C1902356 2024-01-31 P113 115 0.00000

38261 C1902357 2024-02-05 P113 115 1.00000

36695 C1902357 2024-01-29 P114 115 1.00000

35110 C1902357 2024-01-22 P112 115 0.00000

32237 C1902357 2024-01-08 P112 115 1.00000

38016 C1902375 2024-02-02 P112 115 0.00000

35814 C1902376 2024-01-24 P111 115 1.00000

37657 C1902377 2024-02-01 P111 115 0.00000

34734 C1902377 2024-01-18 P111 115 0.00000

35295 C1902378 2024-01-23 P111 115 1.00000

32548 C1902378 2024-01-10 P111 115 1.00000

37694 L3565025 2024-02-01 P111 115 0.00000

34694 L3565025 2024-01-18 P111 115 1.00000

37996 L3565026 2024-02-02 P111 115 1.00000

35729 L3565028 2024-01-24 P111 115 1.00000

37083 D3565029 2024-01-30 P111 115 1.00000

I want to know how many locations check article as marked (IsPresent=1.00000) based on their last visit(DistDate) and selected period by user ( period is calculated from formula selected date on slicer – 30 days). I have relationship between Calendar and DistDate from TableDist1.

There are two steps in calulation:

I must find out max (ID) from table TableDist1 for selected period and for user (UserID) and location (CustomerID):

VAR SummaryResult =

SUMMARIZE (

FILTER (

TableDist1,

TableDist1[DistDate] <= SELECTEDVALUE(Calendar[Date]) &&

TableDist1[DistDate] >= SELECTEDVALUE(Calendar[Date]) - 30

TableDist1[UserID],

TableDist1[CustomerID],

"MaxID", CONVERT(MAX (TableDist1[ID] ),INTEGER)

)

I usually have more then one visit to customer in selected period. For CustomerID= C1902377 and userID=P111, formula has to give next result (based on sample date):

ID	CustomerID	USerID	DateDist
37657	C1902377	P111	2024-02-01
36094

Base on last visit and calculated maxID in step 1, I have to filter TableDist1 with condition: TableDist1(isPresent=1.000) to get products who are checked on last visit. After filtering, I have to calculate DISTINCTCOUNT(TableDist1[CustomerID)) and that is the number that needs me. The number represents number of locations who have had certain products checked based on last visit in selected period.

Data in step 2 who is filtering products based on last visit:

ID	CustomerID	UserID	DateDist	ProductID	isPresent
37657	C1902377	P111	2024-02-01	115	0
33284	CI2995	P111	2024-01-25	101	1

Formula that I have is:

#TblDistKruno3 =

VAR SummaryResult =

SUMMARIZE (

FILTER (

TableDist1,

TableDist1[DistDate] <= SELECTEDVALUE(Calendar[Date]) &&

TableDist1[DistDate] >= SELECTEDVALUE(Calendar[Date]) - 30

TableDist1[UserID],

TableDist1[CustomerID],

"MaxID", CONVERT(MAX (TableDist1[ID] ),INTEGER)

)

Var Dist=SELECTCOLUMNS(TableDist1, "lokacija", TableDist1 [CustomerID], "korisnik", TableDist1[UserID], "artikl", TableDist1[ProductID], "zatecenost", TableDist1 [isPresent], "MaxID", convert(TableDist1[ID],INTEGER))

Var result=CALCULATE(DISTINCTCOUNT(TableDist1[CustomerID]), FILTER(NATURALINNERJOIN(Dist,SummaryResult), [isPresent]=1))

RETURN result

I do not know how to join temotable with first table and then distinctcount(customerID) with filter isPresent=1.

Re: DAX - Get distinctcount of locations based on last visit and joined again with table for filter

Anonymous — Tue, 23 Apr 2024 07:40:14 GMT

Hi kbarilar

I'm a little confused about your requirement, are you getting the maximum ”DistDate“ row for each ”CustomerID“ in a given date range and then calculating how many have “isPresent“ of 1?

In order to help you better, I have the following question to check with you:

33284 does not appear in the sample data you gave, are you talking about 36094?

Best Regards,
Yulia Xu

Re: DAX - Get distinctcount of locations based on last visit and joined again with table for filter

kbarilar — Tue, 23 Apr 2024 10:09:32 GMT

Thank You for helping me and reply. In first step I do not filter products. I just want to get max( DistDate) row for each ”CustomerID“ in a given date range. For customer C1902377 i would get record:

ID CustomerID DistDate UserID

37657 C1902377 2024-02-01 P111

In second step I would take ID 37657 and I would like to join again with TableDIst1 and then filter if some product isPresent. When I join again and filter product 115 with condition isPresent=1, I would not calculate this location in distinctcount number of locations because on last visit on 2024-02-01 isPresent=0:

37657 C1902377 2024-02-01 P111 115 0.00000

Thanks again for helping me.

topic DAX - Get distinctcount of locations based on last visit and joined again with table for filter in DAX Commands and Tips

DAX - Get distinctcount of locations based on last visit and joined again with table for filter

Re: DAX - Get distinctcount of locations based on last visit and joined again with table for filter

Re: DAX - Get distinctcount of locations based on last visit and joined again with table for filter