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

[kbarilar]

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:

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

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

[v-xuxinyi-msft]

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

[kbarilar]

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.