Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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.
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.
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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |