Reply
Frequent Visitor
Posts: 2
Registered: ‎09-10-2018
Accepted Solution

DAX dynamic Run Count bucket % as at certain days

Hi everyone at PowerBi Community, 

 

I have a question. I am very new to DAX, so please bear with me. I read all over the forums, but couldn't get to anything that can answer my question. 

 

Our Company, has a ticket system with a barcode on it. That goes through scanners and it reads the barcodes. After each success hit the ticket get removed and doesn't get scanned again. For example if we have ticket 1 and 2.

 

TicketId ScanOutCome     ScanNum     ScanDate

1                Success                  1              2018-09-10

2                Fail                         1              2018-09-10   

 

TicketId  ScanOutCome    ScanNum      ScanDate

2                Success                  2              2018-09-11

 

This is how it works Smiley Very Happy hope thats simple enough...

 

I need to write some dax that will calculate the number of scan percentages per TicketID grouped together but dynamically.

Let me go more in depth with the data 

 

here is a sample of our daily data

 

TicketIDTicketTypeRunRunOutcomeRunFlagDateRunNumberRunDate
3711381151Fail112018-01-01
3717782561Fail112018-01-01
3693350191Fail112018-01-01
36901062101Fail112018-01-01
3738509151Fail112018-01-01
3684973961Success112018-01-01
3702340591Success112018-01-01
       
TicketIDTicketTypeRunRunOutcome  RunDate
3711381152Fail112018-01-02
3717782562Fail112018-01-02
3693350192Fail112018-01-02
36901062102Fail112018-01-02
3738509152Success112018-01-02
3684973962Not Ran0NullNull
3702340592Not Ran0NullNull
       
TicketIDTicketTypeRunRunOutcomeRunFlagDateRunNumberRunDate
3711381153Fail112018-01-03
3717782563Fail112018-01-03
3693350193Success112018-01-03
36901062103Success112018-01-03
3738509153Not Ran0NullNull
3684973963Not Ran0NullNull
3702340593Not Ran0NullNull
       
TicketIDTicketTypeRunRunOutcomeRunFlagDateRunNumberRunDate
3711381154Fail112018-01-04
3717782564Success112018-01-04
3693350194Not Ran0NullNull
36901062104Not Ran0NullNull
3738509154Not Ran0NullNull
3684973964Not Ran0NullNull
3702340594Not Ran0NullNull
       
TicketIDTicketTypeRunRunOutcomeRunFlagDateRunNumberRunDate
3711381155Success122018-01-04
3717782565Not Ran0NullNull
3693350195Not Ran0NullNull
36901062105Not Ran0NullNull
3738509155Not Ran0NullNull
3684973965Not Ran0NullNull
3702340595Not Ran0NullNull

 

As we can see we have a ticket ID, we have ticket types we have our runoutcomes/runflags on a certain day. This is what I want to get to at the end of a certain day.

 

The percentages of the run counts of all ticket ID's as at a certain day

 

 

 

      
Percent On 2018-01-05     
      
Count 5Count 4Count 3Count 2Count 1 
26.31%21.05%31.56%10.52%10.52% 
      
TicketIDTicketTypeRunCountLastRunDateRunCountPercentage 
37113811552018-01-0426.31% 
37177825642018-01-0421.05% 
36933501932018-01-0315.78% 
369010621032018-01-0315.78%100%
37385091522018-01-0210.52% 
36849739612018-01-015.26% 
37023405912018-01-015.26% 
      
      
Percent On 2018-01-03     
      
Count 5Count 4Count 3Count 2Count 1 
0.00%0.00%60.00%20.00%20.00% 
      
TicketIDTicketTypeRunCountLastRunDateRunCountPercentage 
36933501932018-01-0330.00% 
369010621032018-01-0330.00% 
37385091522018-01-0220.00%100%
36849739612018-01-0110.00% 
37023405912018-01-0110.00% 
      
      
      
      
      

 

 

Guys i know this is a mouth full Smiley Wink

 

but all i want is.

Percent On 2018-01-05    
     
Count 5Count 4Count 3Count 2Count 1
26.31%21.05%31.56%10.52%10.52%
Percent On 2018-01-03    
     
Count 5Count 4Count 3Count 2Count 1
0.00%0.00%60.00%20.00%20.00%
     

Accepted Solutions
Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: DAX dynamic Run Count bucket % as at certain days

@LucianHoward,

Create a date table using DAX below.

Date = CALENDARAUTO()

Then append your tables into a new table named Append1. Create the following measures in the append table.

RunCount = CALCULATE(COUNTROWS(FILTER(Append1,Append1[RunOutcome]<>"Not Ran")),FILTER(Append1,Append1[RunDate]<=SELECTEDVALUE('Date'[Date])))
AllRuncount = SUMX(ALL(Append1),[RunCount])
LastRundate = var selectedvalue=SELECTEDVALUE('Date'[Date]) return CALCULATE(MAX(Append1[RunDate]),ALLEXCEPT(Append1,Append1[TicketID]),filter(Append1,Append1[RunDate]<=selectedvalue))
RunCountPercentage = [RunCount]/[AllRuncount]


Create a slicer using Date field in your date table, and click date value to show relevant values. You can check more details in attached PBIX file.
2.PNG1.PNG

 

 

 



Regards,
Lydia

Community Support Team _ Lydia Zhang
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

Attachment

All Replies
Moderator
Posts: 10,201
Registered: ‎03-10-2016

Re: DAX dynamic Run Count bucket % as at certain days

@LucianHoward,

Create a date table using DAX below.

Date = CALENDARAUTO()

Then append your tables into a new table named Append1. Create the following measures in the append table.

RunCount = CALCULATE(COUNTROWS(FILTER(Append1,Append1[RunOutcome]<>"Not Ran")),FILTER(Append1,Append1[RunDate]<=SELECTEDVALUE('Date'[Date])))
AllRuncount = SUMX(ALL(Append1),[RunCount])
LastRundate = var selectedvalue=SELECTEDVALUE('Date'[Date]) return CALCULATE(MAX(Append1[RunDate]),ALLEXCEPT(Append1,Append1[TicketID]),filter(Append1,Append1[RunDate]<=selectedvalue))
RunCountPercentage = [RunCount]/[AllRuncount]


Create a slicer using Date field in your date table, and click date value to show relevant values. You can check more details in attached PBIX file.
2.PNG1.PNG

 

 

 



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 2
Registered: ‎09-10-2018

Re: DAX dynamic Run Count bucket % as at certain days

Hi v-yuezhe-msft just wanted to say, thanks a million. This really helped me allot.