topic Re: Count number on values (which are measure) in a table in DAX Commands and Tips

Count number on values (which are measure) in a table

Anonymous — Tue, 21 Mar 2023 15:02:16 GMT

I have a table which is the monthly average of a group of data, and at the same time is the hourly average of that month.

Month	Hour	Ave_soure1	Measure_1
Jan	0	-5
Jan	1	-5
Jan	2	-5
Jan	3	11	0.2%
Jan	4	57	1.8%
Jan	5	140	3.6%
Jan	6	76	3.2%
Jan	7	18	1.1%
Jan	8	-5
Jan	9	-5
Jan	10	-5

And I would like to add a column which count each value in my Measure_1 column in this table.

Measure_2 = 
CALCULATE(
    COUNTAX('Fact Table_TIME', [Measure_1]),
    FILTER('Fact Table_TIME', [Hour])
)

Because what I am having is the count of values of the raw data which I use it to construct this average table.

What I have:

Month	Hour	Ave_soure1	Measure_1	Measure_2
Jan	0	-5
Jan	1	-5
Jan	2	-5
Jan	3	11	0.2%	1
Jan	4	57	1.8%	2
Jan	5	140	3.6%	2
Jan	6	76	3.2%	2
Jan	7	18	1.1%	2
Jan	8	-5
Jan	9	-5
Jan	10	-5

I do not know why I am having this result, but what I want it is :

Month	Hour	Ave_soure1	Measure_1	Measure_2
Jan	0	-5
Jan	1	-5
Jan	2	-5
Jan	3	11	0.2%	1
Jan	4	57	1.8%	1
Jan	5	140	3.6%	1
Jan	6	76	3.2%	1
Jan	7	18	1.1%	1
Jan	8	-5
Jan	9	-5
Jan	10	-5

So the total count will be 5.

Re: Count number on values (which are measure) in a table

tamerj1 — Tue, 21 Mar 2023 17:51:04 GMT

Hi Anonymous

please try

Measure_2 =
COUNX ( SUMMARIZE ( 'Fact Table_TIME', [Month], [Hour] ), [Measure_1] )

Re: Count number on values (which are measure) in a table

Anonymous — Wed, 22 Mar 2023 07:50:48 GMT

I couldn't put the filter [Month], because it is in another fact table “Fact Table_DATE", and I saw it is not necessary to put [Month] in this case.
However, may I ask you two questions tamerj1:
1. If I want to add a filter [Month] from another fact table, how I can add it ? (just for knowledge)
2. Can you explain why the function COUNTX and SUMMArize works well in this case?

And thanks for the solution 🙂

Re: Count number on values (which are measure) in a table

tamerj1 — Wed, 22 Mar 2023 08:07:07 GMT

Anonymous
I guess the table names that you are using are somehow confusing. My understanding that both 'Fact Table_DATE & Fact Table_TIME' are actually dim tables that are filtering a fact table (Let's call 'Fact Table'). So you can do

Measure_2 = COUNTX ( SUMMARIZE ( 'Fact Table', 'Fact Table_DATE'[Month], 'Fact Table_TIME'[Hour] ), [Measure_1] )

Summarizing with the right columns is required to achieve the correct granularity at which we want to the measure to be evaluated. Iterating this table will guarantee that the outcome of the evaluation of the measure matches the evaluation at the visual row level. While iteration over the entire table row by row will result in a diffirent evaluation of the measure.