Data from a table (count and sum)

[Syndicate_Admin]

Hello, I have the following table as data

No	Date	Cod. User
1	01/01/2024	1
2	01/01/2024	1
3	03/01/2024	2
4	04/01/2024	3
5	05/01/2024	3
6	01/01/2024	2
7	07/01/2024	4
8	08/01/2024	6
9	08/01/2024	7
10	08/01/2024	1
11	11/01/2024	1
12	12/01/2024	3
13	29/01/2024	3
14	30/01/2024	4
15	31/01/2024	3

I need to get a series of data:
1. For each user, how many registrations are there per day?
Example: User 1 has 4 records
2. For each day, know how many different users have records
Example: for the day 1/1/2024 there are 3 records
For 01/08/2024 there are 3 records
3. From data 2, add the amount
Example: following the previous example, the sum would be 3 + 3 = 6
4. count the days when there are records
Example: according to the previous example, the result would be 2 days.

thank you.

[v-kaiyue-msft]

Hi @Syndicate_Admin ,

Thank you @ChiragGarg2512  for your replies and allow me to provide another insight:

You can do this by creating measure.

Measure =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Cod. User] = MAX('Table'[Cod. User]))))

Measure 2 =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date]))))

Measure 3 =
CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Measure 2] > 1))

Measure 4 =
CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Measure 2] > 1))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

I think we are not understanding each other and I think I also got confused in one of the answers...

Forget option 1 and 2 because I already have them, I attached a screenshot of where you can see them.
Now I am missing 3 and 4, if you look at the screenshot, in the table below, the total says 6, it is not correct, what I want is the sum that according to the screenshot should be 14 if I have not counted wrong.

On the other hand, apart from the total sum, I want the count of days, according to the attached table it should be 11 (these are the days with at least 1 record)

I don't know if the captures have made it better understood.
Thank you.

[v-kaiyue-msft]

Hi @Syndicate_Admin ,

It is a relatively common situation that the measure in the total is incorrect, the measure follows the context of the "Total" row and is calculated in that context. Therefore, measures used in columns of table visualizations may have unexpected values in the Total column.

You can use the following measures, use the IF() + ISINSCOPE() function to determine the data level, and then perform different calculations based on the level.

Measure 2 can be modified to:

Measure 2 =
VAR _count = CALCULATE(COUNT('Table'[No]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date])))
VAR _total_count = COUNT('Table'[No])
RETURN
IF(ISINSCOPE('Table'[Date]),_count,_total_count)

If you want to calculate the number of days with at least 1 record, you can modify measure 4 to:

Measure 4 =
CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[Measure 2] >= 1))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Could you briefly explain Measure 2 to me, I don't really understand what they do.
Anyway, I see that it doesn't add up, Measure 2 says that on 01/01/2024 there are three different users when there are only two (user 1 and 2)
Measure 4 is already clear to me and it is correct.

[v-kaiyue-msft]

Hi @Syndicate_Admin ,

I modified measure 2 and added some more comments.

Measure 2 =
VAR _count = CALCULATE(DISTINCTCOUNT('Table'[Cod. User]),FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date]))) //Counts the different user codes for the current date
VAR _table = SUMMARIZE('Table','Table'[Date],'Table'[Cod. User]) // Create virtual table to get table with unique date and user code values
VAR _total_count = COUNTROWS(_table) // Count the number of rows in the virtual table
RETURN
IF(ISINSCOPE('Table'[Date]),_count,_total_count) // Display different data in layers, date layer and total layer display different counts

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

More information about the ISINSCOPE function can be found in the documentation: ISINSCOPE function (DAX) - DAX | Microsoft Learn.

[Syndicate_Admin]

Sorry, I don't understand the VAR _count part when you add the
FILTER(ALL('Table'),'Table'[Date] = MAX('Table'[Date]))

[v-kaiyue-msft]

Hi @Syndicate_Admin ,

The calculated column can directly obtain the current value, but meausre requires an aggregate function such as max() to obtain the current value. Therefore, when performing group calculation operations, measure needs to use the max function to obtain the current value, and only the same values will be calculated together.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

In my real case, I have the table and the date column is related to a date table.
So should I put this last part like this?
FILTER(ALL(Cab_act_almacen_reg_sql),Cab_act_almacen_reg_sql[Registering Date] = MAX(Fechas[Date]))

Table Cab_act_almacen_reg_sql would be the one for records
Dates[Date] would be the dates related to Cab_act_almacen_reg_sql[Registering Date]

If I do as I attached, the total does not do it correctly, yes by rows but the total does as before.

Here is all my measure:

Measura 2 = 
VAR _count = CALCULATE(DISTINCTCOUNT(Cab_act_almacen_reg_sql[Cod_ operario almacen]),FILTER(ALL(Cab_act_almacen_reg_sql),Cab_act_almacen_reg_sql[Registering Date] = MAX(Fechas[Date]))) //Counts the different user codes for the current date
VAR _table = SUMMARIZE(Cab_act_almacen_reg_sql,Cab_act_almacen_reg_sql[Cod_ operario almacen]) // Create virtual table to get table with unique date and user code values
VAR _total_count = COUNTROWS(_table) // Count the number of rows in the virtual table
RETURN
IF(ISINSCOPE(Fechas[Date]),_count,_total_count) // Display different data in layers, date layer and total layer display different counts

Thank you.

[ChiragGarg2512]

1)  Measure = CALCULATE(COUNTROWS('Table'), REMOVEFILTERS('Table'[No], 'Table'[Date]))

2) Measure2 = CALCULATE(COUNTROWS('Table'), REMOVEFILTERS('Table'[No], 'Table'[Cod. User]))

3) and 4) are a bit unclear

[Syndicate_Admin]

Let's explain it in another way to see if it can be understood better:
Point 3 is basically the sum of records
Point 4, of the entire month (in this example case it is January), how many days there are with at least 1 record, that is, of the 31 days that January has, how many of them there are any records.

[ChiragGarg2512]

3) Simple Countrows should suffice

4)  Apply CountRows on the table where the date is between the start and end of the month.

[Syndicate_Admin]

2) The measure provided above does not count the users per day with registrations, it only does the sum of registrations per day.
3) if correct, a count already adds up the records per day
4) I don't really understand what you mean.