COUNTAX DAX Formel in Power BI Desktop need explanation

[Vlad_Akka]

Hello all.

I have two Lookup tables. One of it has data about learning courses (number of course, Course Start data, the quantitiy of the participants etc). The second one is a person table with learning courses in which they have participated.

The both of the tables are exported from Excelsheets.

"Cousrse Groups Lookup " Table

CS Course number	Date of Start	Date of End	Course activity state	Participants Quantitiy	Participants successful Quantitiy	KPI
1	04.09.2024	25.02.2024	ended	5	5	Not used now
2	27.03.2024	08.05.2024	ended	22	21	Not used now
3	15.05.2024	15.02.2025	active	6	0	Not used now

"Person List Lookup " Table

Personal Nr	Persons	CS Course number	Participation state	Country
1	Lionel	1	1	France
2	Laurence	1	1	France
3	François, Vetea	1	1	France
4	Sashko	1	1	France
5	Vetea	1	1	France
6	Schreiner	2	1	Germany
7	Schneider	2	1	Germany
8	Schindler	2	1	Germany
9	Backenbauer	2	1	Germany
10	Ivaschko	2	1	Germany
11	Lindnder	2	1	Germany
12	Musterman	2	1	Germany
13	Musterman1	2	1	Germany
14	Müller	2	1	Germany
15	Schmidt	2	1	Germany
16	Fischer	2	1	Germany
17	Weber	2	1	Germany
18	Meyer	2	1	Germany
19	Wagner	2	1	Germany
20	Becker	2	1	Germany
21	Schulz	2	1	Germany
22	Bauer	2	1	Germany
23	Schäfer	2	1	Germany
24	Koch	2	1	Germany
25	Schröder	2	1	Germany
26	Neumann	2	1	Germany
27	Zimmermann	2	2	Germany
28	Braun	3	3	Germany
29	Krüger	3	3	Germany
30	Hofmann	3	3	Germany
31	Klein	3	3	Germany
32	Schmitt	3	3	Germany
33	Bley	3	3	Germany
34	Schmitz	0	4	Germany
35	Krause	0	4	Germany
36	Schulze	0	4	Germany
37	Schuster	0	4	Germany
38	Köhler	0	4	Germany
39	König	0	4	Germany
40	Winter	0	4	Germany
41	Schach	0	4	Germany
42	Huber	0	4	Germany
43	Kaiser	0	4	Germany
44	Fuchs	0	4	Germany
45	Peters	0	4	Germany
46	Lang	0	4	Germany
47	Möller	0	4	Germany
48	Weiß	0	4	Germany
49	Jung	0	4	Germany
50	Hahn	0	4	Germany
51	Schubert	0	4	Germany
52	Vogel	0	4	Germany
53	Vogler	0	4	Germany
54	Keller	0	4	Germany
55	Berger	0	4	Germany
56	Winkler	0	4	Germany
57	Roth	0	4	Germany
58	Beck	0	4	Germany
59	Schumacher	0	4	Germany
60	Groß	0	4	Germany
61	Seidel	0	4	Germany
62	Ziegler	0	4	Germany
63	Kuhn	0	4	Germany
64	Bergmann	0	4	Germany
65	Pfeiffer	0	4	Germany
66	Novak	0	4	Czech Republic
67	Noak	0	4	Czech Republic
68	Pietsch	0	4	Czech Republic
69	Kowalski	0	4	Czech Republic

(no worry, all names are used for example)

In the "Course Groups Lookup" table i have the column "Participants Quantitiy" yet. But it is calculated and prompt manually.

Every time if the new course begin, the neu column shall be added into the Table "Course Group Lookup" with recalculation, based on the data in the "Person List Lookup" Table. The both tables have one relationship 1 : x. See the figure below

My goal is to add the column  "Participants Quantitiy CC" with a measure, which just calculate the participants quantity for every learning course (see the figure).

Of course i could use the DAX Formula CALCULATE with FILTER and logical conditions. But in this case i shall add the logic if a new learning course begin and this is not what i want.

If you will see attantively on the measure itself, and on the result in the added column, probably you will come to the same conclusion as i - it doesn´t work because i have nearly 30 persons with the course number "0" and due to the logic of the formula COUNTAX i shall have for all of the learning courses 30 persons. But you will be wery surprised because it works (see the measure below and the content of the column in the picture above).

EXAMPLE of Quantity =   COUNTAX ( 'Person List Lookup', 'Person List Lookup'[CS Course number] = 0 ) 

There is no matter if i compare with 0 or change the condition to less as 2. The result will be the same - it works as expected.

 Truly says, i have no idea, but i will be appraciate to you to hear your explanations.

[v-yangliu-msft · Accepted solution]

Hi  @Vlad_Akka ,

Measure and calculated columns have different row contexts, and the value of the calculated column is calculated based on the table context and is a fixed value.

Refer to:

Solved: Using a Measure in a Calculated Column - Microsoft Fabric Community

You may consider the following formula for calculating column dax:

Column =
COUNTX(
    FILTER(ALL('Person List Lookup'),
    'Person List Lookup'[CS Course number]=EARLIER('Cousrse Groups Lookup'[CS Course number])),'Person List Lookup'[Personal Nr])

This is the related document, you can view this content:

Context in DAX Formulas | Microsoft Learn

Calculated Columns and Measures in DAX - SQLBI

Column and measure references in DAX - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

[Vlad_Akka]

Thank you for your time and for the help. It was helpful, but i am still wondering why the DAX formula in my question works