Distinct Count of Triggers

[Anonymous]

Hi I have below table on PowerBI to demonstrate the High, medium, and low spenders..

 DAX used for columns are;

HIGH SPEND = IF(AND(OR([1M Rolling SPEND]>=50000,[1D Rolling SPEND]>=20000),[Date]>=DATE(2021,5,17)),"Flag","")

HIGH SPEND FLAG count per player = IF(AND('Database'[HIGH SPEND FLAG count per player part 1]=2,COUNTROWS(FILTER('Database',[Number]=EARLIER('Database'[Number]) && 'Database'[HIGH SPEND FLAG count per player part 1]=1))=0),1,'Database'[HIGH SPEND FLAG count per player part 1])

HIGH SPEND FLAG count per player part 1 = IF([HIGH SPEND FLAG]=BLANK(),BLANK(),COUNTROWS(FILTER('Database',[Number]=EARLIER('Database'[Number]) && [HIGH SPEND FLAG]="FLAG" && [Date]<=EARLIER('Database'[Date]))))

i am trying to get the results as like on 3rd column HIGH SPEND FLAG Count Per Player(EXPECTED) but above 2 formulas on 1st and 2nd column don't give me results how i want. 

One of the reason is Employee 1111 was on both locations (X,Y) on 20/04/2022 and has been flagged twice and my current DAX filter doesn't filter it.

I am thinking DistinctCount formula might be the solution but but not sure about it.

Anyone available for help please?

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could add fx funtion in power query .

= Table.Group(#"Sorted Rows", {"Number"}, {{"A", each Table.AddIndexColumn(_, "Index",1,1), type table}})

then expand it.

Or create  a rank column in dax.

rank = RANKX(FILTER(ALL('Database'),[Number]=EARLIER([Number])),FORMAT([Date],"yyyy-mm-dd")&[Location],,ASC)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yalanwu-msft 

Sorry for the late reply,

rank = RANKX(FILTER(ALL('Database'),[Number]=EARLIER([Number])),FORMAT([Date],"yyyy-mm-dd")&[Location],,ASC)

work perfectly.

Much appreciated for your time and solution.

Thanks

[v-yalanwu-msft]

Hi, @Anonymous ;

You could after sort then add index column.

Then add index column and create a HIGH SPEND FLAG count per player part1 column.

Or you could create a rank column.

rank = RANKX('Table',FORMAT( [Date],"yyyy-mm-dd")&[Location],,ASC)

Then create a HIGH SPEND FLAG count per player part1 column

HIGH SPEND FLAG count per player part11 = 
IF([HIGH SPEND FLAG]=BLANK(),BLANK(),
  COUNTROWS(FILTER('Table',[Number]=EARLIER('Table'[Number]) && [HIGH SPEND FLAG]="FLAG" && [Date]<=EARLIER('Table'[Date])&&[rank]<=EARLIER([rank]))))

when there are more than one "Spenders" and they can be at 3 different location same day which will be flagged on all of them if their spend goes above £60k at the end of the Day.

I don't quite understand. Could you tell me your logic and the output you want?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a index column in power query. then create column by dax.

HIGH SPEND FLAG count per player part 1 = 
IF([HIGH SPEND FLAG]=BLANK(),BLANK(),
  COUNTROWS(FILTER('Database',[Number]=EARLIER('Database'[Number]) 
    && [HIGH SPEND FLAG]="FLAG" 
    && [Date]<=EARLIER('Database'[Date])&&[Index]<=EARLIER([Index]))))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft 

Many thanks for your reply and the solution, appreciated. i went back and apply the formula to my original file but gave me the below;

I believe you have a solution for that too 🙂

Thanks

The slolution you provided absolutely beautiful but seems doesn't fit the purpose when there are more than one "Spenders" and they can be at 3 different location same day which will be flagged on all of them if their spend goes above £60k at the end of the Day. My bad didn't mention more details on about it.