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,
I'm facing a problem with a table in which I'd like to calculate the number of rows that exist until a given date. My table has the following structure:
Unique_identifier Updation_date Source
123 01/01/1999 A
242 01/02/1999 B
264 01/03/1999 A
And what I'd like to get is, for each row, the number of registries that are included until this row. For example, for second row, given that its Updation_date is 01/02/1999, I'd like to obtain two, because there are two rows that are included until that date: 01/01/1999 and the record date itself: 01/02/1999. I've tried with the following formula:
Unique_identifier Updation_date Source My_measure
123 01/01/1999 A 1
242 01/02/1999 B 2
264 01/03/1999 A 3
Could you please give me a hand? I think this is not such a difficult problem, but I can't get a solution :(.
Solved! Go to Solution.
You could add a calculated column (Table name is "Registries")
Count = VAR UpdationDate = Registries[Updation_date] RETURN COUNTROWS( FILTER(Registries,Registries[Updation_date] <= UpdationDate))
That should result in:
Hope this helps
JJ
Hi
I don't want to seem pedantic but first let's clarify on 2 terms
- If you add a new column o a table it's just that: a calculated column and NOT a measure
- A unique identifier holds unique values by its very definition.
I am not aware of a way to do what you want to do. Power BI does not read the rows in the order as they are displayed in the Data pane (it is a column based system after all). Without a true unique identifier Power BI has no way to know which 242 is first and which is second. You would need a logic derived from the data that determines the order of the entries.
You could add a calculated column (Table name is "Registries")
Count = VAR UpdationDate = Registries[Updation_date] RETURN COUNTROWS( FILTER(Registries,Registries[Updation_date] <= UpdationDate))
That should result in:
Hope this helps
JJ
Hi @DoubleJ . Your solution worked like a charm. Now I'm facing another situation, and I'd like to know if it's possible to implement it. Let's suppose that some of the rows are duplicated in any of its values. For example, there are two different entries for the unique_identifier 123:
Unique_identifier Updation_date Source My_new_measure
123 01/01/1999 A 1
242 01/02/1999 B 2
264 01/03/1999 A 3
What I want is My_new_measure to count only the number of unique_identifiers before date. For example, for the first two rows there is only one unique_identifier, so My_new_measure should be 1 in both cases. And, for example, if I add a fifth row whose identifier already exists (242, for example), the measure shouln't increase, because the identifier in the row did already exist. A sixth row with a new unique identifier should result in an increase. I'll show you with an example:
Unique_identifier Updation_date Source My_new_measure
123 01/01/1999 A 1
123 01/02/1999 A 1 -->the count maintains because the identifier already exists (123)
242 01/02/1999 B 2 -->the count increases: new identifier
264 01/03/1999 A 3 -->the count increases:new identifier
242 12/12/1999 A 3 -->the count maintains because the identifier already exists (242)
325 12/01/2000 A 4 -->the count increases:new identifier
Thanks all for your help 🙂
You could add a column, but I recommend you don't.
Learn about calculated columns vs measures in Power BI
This is a very complex issue that I wont go into. The solution to your problem is to
You can learn about calendar tables in Power BI at my blog
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 |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |