cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

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

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.

2 REPLIES 2
Community Support

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

New Member

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors