COUNTAX DAX Formel in Power BI Desktop need explanation
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.
Count a Measure that uses YTD
Hi All! Hope you guys can help me with this one. I'm trying to figure out how many customers were won or lost on a specific month and then aggregate on higher levels. The image above shows the 2 tables I'm using: d_Calendar: date dimension (joined by [Date] and [Calendar Month] f_Customer Profitability: fact table with sales by customer and by date. Each customer has some regions and other aggregations, but [Name and Number] are the lowest level. In order to evaluate if the customer was lost or won, I'm using the company's rule and created the following measures: # Net Sales Actual Month Net Sales: Actual = SUM('f_Customer Profitability'[Net Sales]) # Year-to-Date Net Sales (Sum from January till Actual Month) Net Sales: Actual (YTD) = TOTALYTD( SUM('f_Customer Profitability'[Net Sales]), d_Calendar[Date], ALL('d_Calendar') ) # Average to Date Net Sales (Average from January till Actual Month) Net Sales: Actual (ATD) = TOTALYTD( AVERAGE('f_Customer Profitability'[Net Sales]), d_Calendar[Date], ALL('d_Calendar') ) # Last Year's Year-to-Date Net Sales (Sum from January till Actual Month, but Last Year) Net Sales: Previous Year (YTD) = CALCULATE([Net Sales: Actual (YTD)], SAMEPERIODLASTYEAR(d_Calendar[Date])) Next, with these measures (which are working fine and I've confirmed the results with an Excel file), I calculate whether the customer is a Win or a Loss with the following measure: Win Or Loss = // Loss IF([Net Sales: Previous Year (YTD)] > 5000 && [Net Sales: Actual] < 1, "Loss", // Win IF([Net Sales: Previous Year (YTD)] < 1000 && [Net Sales: Actual (ATD)] > 1000, "Win", // Else BLANK() )) The Win/Loss result must be calculated on the Customer level ([Name and Number]), which is the lowest level in the table. This measure is working as well and I'm getting the following results in a Matrix: I believe the problem starts here: Then I want to count them and aggregate them on a higher level, for instance [Region Code], and see which Region has higher Win/Loss. The problem is, I want to count how many wins and how many losses in a specific month I had, by [Region Code]. I tried using the following Measure, but it is not working. Basically, I want to count the results of a measure. Count Wins = COUNTAX('f_Customer Profitability', [Win Or Loss] = "Win") I think this is not working because the YTD and ATD won't work on a row by row count. Could you please help? 🙂
