countax

6 Topics

Differences COUNTAX and COUNTROWS
Hi everyone, I am currently doing the Master DAX course by SQLBI. In exercise 03.30, I attempted a solution with COUNTAX but this yielded a different result than the actual solution that used COUNTROWS. Apparently, I am missing something that I need to consider when applying COUNTAX. The task in 03.30 is as follows: Create a # CustWithChildren measure that computes the number of customers with children – only consider customers where Customer[Total Children] is greater than zero. My DAX is as follows: #CustWithChildren (Me) = countax( customer, Customer[Total Children] >0 ) You can find the PBI file for exercise 03.30 here . Can someone please explain to me why my attempt did not arrive at the correct numbers? Thanks in advance!
Solved
ThomasSan
2 years ago Place DAX Commands and Tips
1.1KViews
0likes
3Comments
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.
Solved
Vlad_Akka
2 years ago Place DAX Commands and Tips
763Views
0likes
2Comments
Count number on values (which are measure) in a table
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.
Solved
Anonymous
3 years ago Place DAX Commands and Tips
887Views
0likes
3Comments
I want to create a table for a report where I can get the count of uses connected by hours
I have a table with users that start and end a connection Table1 Users START_TIME_UTC END_TIME_UTC User1 2019-07-12 01:00:31.000 2019-07-12 04:30:00.000 User2 2019-07-12 02:22:00.000 2019-07-12 04:22:29.000 User1 2019-07-12 03:14:28.000 2019-07-12 04:20:00.000 User3 2019-07-12 01:05:00.000 2019-07-12 06:05:24.000 I create a table in DAX with DATETIME increasing un hour until today Table2 DATETIME COUNTUSERSCONNECT 2019-07-12 01:00:00 0 2019-07-12 02:00:00 2 2019-07-12 03:00:00 3 2019-07-12 04:00:00 4 2019-07-12 05:00:00 1 2019-07-12 06:00:00 1 2019-07-12 07:00:00 0 I don't know how calculate or COUNTUSERSCONNECT, it should check datetime with table1 and count how many users are connected in this hour. I try it, but doesn't work COUNTUSERSCONNECT = COUNTAX ( table1, AND ( table1[START_TIME_UTC] < table2[DateTime], table1[END_TIME_UTC] > table2[DateTime] ) ) It count everthing.
Solved
luisecp
4 years ago Place DAX Commands and Tips
903Views
0likes
2Comments
How to count it units>0
Hi guys, Data is stored in below formats but I have to count at summary 2 level if units>0. Raw data Code Units AP50002862105 1 AP50002862105 -1 AP52001160899 3 BI80000160943 6 I have to count at Code level if units>0 Code Units final output AP50002862105 0 0 AP52001160899 3 1 BI80000160943 6 1 2 Please suggest. Thanks a lots. Tanushree_Kapse amitchandak Greg
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.3KViews
0likes
6Comments
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? 🙂
Solved
Anonymous
5 years ago Place DAX Commands and Tips
2KViews
0likes
4Comments