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.
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 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
TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
37113811 | 5 | 1 | Fail | 1 | 1 | 2018-01-01 |
37177825 | 6 | 1 | Fail | 1 | 1 | 2018-01-01 |
36933501 | 9 | 1 | Fail | 1 | 1 | 2018-01-01 |
36901062 | 10 | 1 | Fail | 1 | 1 | 2018-01-01 |
37385091 | 5 | 1 | Fail | 1 | 1 | 2018-01-01 |
36849739 | 6 | 1 | Success | 1 | 1 | 2018-01-01 |
37023405 | 9 | 1 | Success | 1 | 1 | 2018-01-01 |
TicketID | TicketType | Run | RunOutcome | RunDate | ||
37113811 | 5 | 2 | Fail | 1 | 1 | 2018-01-02 |
37177825 | 6 | 2 | Fail | 1 | 1 | 2018-01-02 |
36933501 | 9 | 2 | Fail | 1 | 1 | 2018-01-02 |
36901062 | 10 | 2 | Fail | 1 | 1 | 2018-01-02 |
37385091 | 5 | 2 | Success | 1 | 1 | 2018-01-02 |
36849739 | 6 | 2 | Not Ran | 0 | Null | Null |
37023405 | 9 | 2 | Not Ran | 0 | Null | Null |
TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
37113811 | 5 | 3 | Fail | 1 | 1 | 2018-01-03 |
37177825 | 6 | 3 | Fail | 1 | 1 | 2018-01-03 |
36933501 | 9 | 3 | Success | 1 | 1 | 2018-01-03 |
36901062 | 10 | 3 | Success | 1 | 1 | 2018-01-03 |
37385091 | 5 | 3 | Not Ran | 0 | Null | Null |
36849739 | 6 | 3 | Not Ran | 0 | Null | Null |
37023405 | 9 | 3 | Not Ran | 0 | Null | Null |
TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
37113811 | 5 | 4 | Fail | 1 | 1 | 2018-01-04 |
37177825 | 6 | 4 | Success | 1 | 1 | 2018-01-04 |
36933501 | 9 | 4 | Not Ran | 0 | Null | Null |
36901062 | 10 | 4 | Not Ran | 0 | Null | Null |
37385091 | 5 | 4 | Not Ran | 0 | Null | Null |
36849739 | 6 | 4 | Not Ran | 0 | Null | Null |
37023405 | 9 | 4 | Not Ran | 0 | Null | Null |
TicketID | TicketType | Run | RunOutcome | RunFlag | DateRunNumber | RunDate |
37113811 | 5 | 5 | Success | 1 | 2 | 2018-01-04 |
37177825 | 6 | 5 | Not Ran | 0 | Null | Null |
36933501 | 9 | 5 | Not Ran | 0 | Null | Null |
36901062 | 10 | 5 | Not Ran | 0 | Null | Null |
37385091 | 5 | 5 | Not Ran | 0 | Null | Null |
36849739 | 6 | 5 | Not Ran | 0 | Null | Null |
37023405 | 9 | 5 | Not Ran | 0 | Null | Null |
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 5 | Count 4 | Count 3 | Count 2 | Count 1 | |
26.31% | 21.05% | 31.56% | 10.52% | 10.52% | |
TicketID | TicketType | RunCount | LastRunDate | RunCountPercentage | |
37113811 | 5 | 5 | 2018-01-04 | 26.31% | |
37177825 | 6 | 4 | 2018-01-04 | 21.05% | |
36933501 | 9 | 3 | 2018-01-03 | 15.78% | |
36901062 | 10 | 3 | 2018-01-03 | 15.78% | 100% |
37385091 | 5 | 2 | 2018-01-02 | 10.52% | |
36849739 | 6 | 1 | 2018-01-01 | 5.26% | |
37023405 | 9 | 1 | 2018-01-01 | 5.26% | |
Percent On 2018-01-03 | |||||
Count 5 | Count 4 | Count 3 | Count 2 | Count 1 | |
0.00% | 0.00% | 60.00% | 20.00% | 20.00% | |
TicketID | TicketType | RunCount | LastRunDate | RunCountPercentage | |
36933501 | 9 | 3 | 2018-01-03 | 30.00% | |
36901062 | 10 | 3 | 2018-01-03 | 30.00% | |
37385091 | 5 | 2 | 2018-01-02 | 20.00% | 100% |
36849739 | 6 | 1 | 2018-01-01 | 10.00% | |
37023405 | 9 | 1 | 2018-01-01 | 10.00% | |
Guys i know this is a mouth full
but all i want is.
Percent On 2018-01-05 | ||||
Count 5 | Count 4 | Count 3 | Count 2 | Count 1 |
26.31% | 21.05% | 31.56% | 10.52% | 10.52% |
Percent On 2018-01-03 | ||||
Count 5 | Count 4 | Count 3 | Count 2 | Count 1 |
0.00% | 0.00% | 60.00% | 20.00% | 20.00% |
Solved! Go to Solution.
@Anonymous,
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.
Regards,
Lydia
@Anonymous,
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.
Regards,
Lydia
Hi v-yuezhe-msft just wanted to say, thanks a million. This really helped me allot.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |