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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Distinct Count of Triggers

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

Ralf8522_2-1650896235176.png

 

 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]))))

 

Ralf8522_1-1650895837866.png

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

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1651215336748.png

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:

vyalanwumsft_1-1651217328962.png


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.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1651215336748.png

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:

vyalanwumsft_1-1651217328962.png


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
Not applicable

@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
Community Support
Community Support

Hi, @Anonymous ;

You could after sort then add index column.

vyalanwumsft_1-1651140477883.png

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
Community Support
Community Support

Hi, @Anonymous ;

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

vyalanwumsft_0-1651123303572.png

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:

vyalanwumsft_1-1651123539036.png


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
Not applicable

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

 

 

Ralf8522_1-1651137197665.png

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors