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 everyone,
I am trying to count the number of recurrences over weeks and also create a new column which concatenate the dates of the report when the data appeared.
From a fact table, I created a filter table:
From this new table, I used the GROUP BY function:
KEY PO LINE | Date Cancellation Report |
C0909 | 02/08/2019 |
CR560 | 02/08/2019 |
CE455 | 02/08/2019 |
C0909 | 09/08/2019 |
CR560 | 09/08/2019 |
CF433 | 09/08/2019 |
KEY PO LINE | RECURRENCE | Date Cancellation Report |
C0909 | 2 | 02/08/2019 - 09/08/2019 |
CR560 | 2 | 02/08/2019 - 09/08/2019 |
CE455 | 1 | 02/08/2019 |
CF433 | 1 | 09/08/2019 |
Thanks for your help!
Kind regards
Watou
Hi,
Drag Key PO Line to the Row labels and write these measures:
=COUNTROWS(Data)
=MIN(Data[Date Cancellation Report])&if(MAX(Data[Date Cancellation Report])-MIN(Data[Date Cancellation Report])=0,BLANK()," - "&MAX(Data[Date Cancellation Report]))
Hope this helps.
Hi,
Thank you. Actually, the countrows does the job by counting the number of recurrences.
I used this Concatenate function as following
I got this table:
Key PO Line | Concatenate | # Nb Lines |
C0225912 | 26/07/2019- 31/07/2019- 02/08/2019- 09/08/2019 | 4 |
C08322445 | 26/07/2019- 31/07/2019 | 2 |
I would like to show data in this table only when the latest date (for instance 09/08/2019 in this example) is included in the concatenate column.
Is it possible to go around with this with a measure or filter?
Thanks again,
Hi,
I'm not sure of who you are replying to. Did you try out my solution?
Hello Ashish,
Sorry for my late reply, I was away for a couple of weeks.
I tried out your solutions and I could get the number of recurrences with the COUNTROWS and also get the range MIN-MAX with the report date. However, would it be possible:
- to have the full range of dates displayed (not only MIN & MAX)
- to only display the data that contains the filtered date in the slicer
Otherwise, would it be possible to get this result directly from the table by Grouping by: KEY PO LINE, New column "Count Recurrence" and new column "Concatenate" that concatenates all the cancellation date reports?
Thanks again for your help.
Sébastien
Hi,
You are picking up this thread after a month. Please show the dataset and the expected result.
Hi Ashish,
I will be using the same as my initial post.
I am just wondering if there is a quick way to create a new table using the GROUP BY function that will create the 2 new columns: "Count of recurrences" + "Concatenate Date cancellation report"
Or is the only way to get this result through measures?
Thanks
KEY PO LINE | Date Cancellation Report |
C0909 | 02/08/2019 |
CR560 | 02/08/2019 |
CE455 | 02/08/2019 |
C0909 | 09/08/2019 |
CR560 | 09/08/2019 |
CF433 | 09/08/2019 |
KEY PO LINE | RECURRENCE | Concatenate Date Cancellation Report |
C0909 | 2 | 02/08/2019 - 09/08/2019 |
CR560 | 2 | 02/08/2019 - 09/08/2019 |
CE455 | 1 | 02/08/2019 |
CF433 | 1 | 09/08/2019 |
Hi,
That is what my formula (already shared with you) does.
Try:
Date Cancellation Report = VAR _Line = 'Outstanding PO Query'[KEY PO LINE] RETURN _LINE & IF ( 'Outstanding PO Query'[RECURRENCE] > 1, " - " & CALCULATE ( MAX ( 'Outstanding PO Query'[Date Cancellation Report] ), ALL ( 'Outstanding PO Query' ), 'Outstanding PO Query'[KEY PO LINE] = _VAR ), BLANK () )
Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!
Stay up to date on
Read my blogs on
Remember to spread knowledge in the community when you can!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
Thanks Steve, however I am not sure to get it.
Should I do this calculation from the fact table? What will this give to me?
Sorry I am still novice in BI.
Thanks
For adding a concatenation column, just wrap your GROUPBY with an ADDCOLUMNS function.
Thanks for your reply. I am still novice in Power BI and cannot get what I want. I am getting several times the same data in the Concatenate while I would like only 1 data for each cancellation date. This is an example of what I got:
LINE | RECURRENCE | Date Cancellation Report |
C0909 | 2 | 02/08/2019 - 02/08/2019 - 02/08/2019 - 02/08/2019 - 09/08/2019- 09/08/2019- 09/08/2019 |
I would like something similar to this:
LINE | RECURRENCE | Date Cancellation Report |
C0909 | 2 | 02/08/2019 - 09/08/2019 |
Here is the formula I typed:
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |