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.
Hello!
I am trying to take a dataset with dates and id numbers for transcations and summarize in to table that count the transcations and receipts that occured on a particular day. Essentialy the issue I am running in to is the dates need to be combined into to one and the distinct count of ids needs to show in a table. The catch is that slicers from the base table still need to be able to apply to this dataset. (In the example set I provided, I would slice by partner in the report if that is what I needed to do).
Here is a mock of what the columns coming in that are applicable to my question look lilke.
CustomerID | TransID | TransDate | Partner | Receipt | ReceiptReceived |
1 | AA123456 | 1/1/2018 | A | ||
2 | AA123457 | 1/1/2018 | B | ||
3 | AA123458 | 1/1/2018 | B | 1234 | 1/1/2018 |
4 | AA123459 | 1/2/2018 | A | ||
5 | AA123460 | 1/2/2018 | A | 1236 | 1/3/2018 |
6 | AA123461 | 1/2/2018 | A | ||
7 | AA123462 | 1/3/2018 | B | 1238 | 1/3/2018 |
8 | AA123463 | 1/3/2018 | B | 1239 | 1/10/2018 |
9 | AA123464 | 1/3/2018 | A | ||
10 | AA123465 | 1/4/2018 | A | 1241 | 1/6/2018 |
11 | AA123466 | 1/4/2018 | A | ||
12 | AA123467 | 1/6/2018 | B | ||
13 | AA123468 | 1/6/2018 | B | 1244 | 1/6/2018 |
14 | AA123469 | 1/6/2018 | A | ||
15 | AA123470 | 1/7/2018 | A | 1246 | 1/6/2018 |
16 | AA123471 | 1/7/2018 | A | ||
17 | AA123472 | 1/7/2018 | B | ||
18 | AA123473 | 1/7/2018 | B | 1249 | 1/11/2018 |
19 | AA123474 | 1/7/2018 | A | ||
20 | AA123475 | 1/7/2018 | A | 1251 | 1/7/2018 |
The end result should look something like this:
Date | Transactions | ReceiptsReceived |
1/1/2018 | 3 | 1 |
1/2/2018 | 3 | 0 |
1/3/2018 | 3 | 2 |
1/4/2018 | 2 | 0 |
1/5/2018 | 0 | 0 |
1/6/2018 | 3 | 3 |
1/7/2018 | 6 | 1 |
1/8/2018 | 0 | 0 |
1/9/2018 | 0 | 0 |
1/10/2018 | 0 | 1 |
1/11/2018 | 0 | 1 |
Any and all help is appreciated!
-Josh
Solved! Go to Solution.
Hi,
You may refer to my solution here.
Hope this helps.
Hi,
You may refer to my solution here.
Hope this helps.
Both great responses! I went with Ashish's version as it allowed the total columns to work.
A follow up question.. how can I create a measure to divide the count of the transactions by the receipts? I guess the UseRelationship functions are giving me trouble when I try to do so. The error said that column cannot be found or used in this calculation.
Thanks!
Hi,
What happens when you try this measure
=[Transactions]/[Receipts received]
I believe that you want measures like this:
Transactions = VAR trans = DISTINCTCOUNT([CustomerID]) RETURN IF(ISBLANK(trans),0,trans) ReceiptsReceived = VAR receipts = CALCULATE(COUNT('#Receipts'[ReceiptReceived]),FILTER(ALL('#Receipts'),'#Receipts'[ReceiptReceived]=MAX('#Receipts'[TransDate]))) RETURN IF(ISBLANK(receipts),0,receipts)
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |