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

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

Reply
kbarilar
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
v-xuxinyi-msft
Community Support
Community Support

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?

vxuxinyimsft_0-1713857601684.png

 

Best Regards,
Yulia Xu

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.