DAX equivalent of GROUP BY and MIN - How do I count products launched, by year?
I want to create a measure that counts how many products were sold for the first time in a given year. That means if the product was sold in any year prior, it should not count in the current year. I would expect to do something like this: (code can be run and iterated here: [https://dax.do/fDTAmTl3kDIMaF/][1] ) DEFINE MEASURE 'Sales'[m1] = CALCULATE ( DISTINCTCOUNT (Sales[ProductKey]), SUMMARIZECOLUMNS ( Sales[ProductKey], CALCULATETABLE ( Sales, ALL( Sales[Order Date] ), ALL( 'Date'[Date] ), ALL( 'Date'[Calendar Year] ) ), "earliest_sale", MIN( Sales[Order Date] ) ), ALL( Sales[Order Date] ), ALL( 'Date'[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "number of products sold for the first time", 'Sales'[m1] ) However, this returns the following: Which is exactly the same result as the one I get from EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], 'Sales', "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "total sales", COUNTROWS ( 'Sales' ) ) Finally, going perhaps, a little bit crazy, I tried this: EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], FILTER( 'Sales', Sales[Order Date] = CALCULATE( MIN( Sales[Order Date] ), SUMMARIZE ( CALCULATETABLE ( 'Sales', ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ), Sales[ProductKey] ), ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ) ), "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "sales", COUNTROWS ( 'Sales' ) ) And got: Any help would be much appreciated. I wan to count the number of products in each year that were never sold before then, i.e. were sold in that year, for the first time ever. [1]: https://dax.do/fDTAmTl3kDIMaF/
Group by one column and get average from another column
Hello all I have following table and I want to get with DAX avg for each character, so result should be this unfornatelly GROUP BY is not working as I expect..can anyone help me to understand where I am wrong? and what should be correct formula ?DAX table to create a revenue funnel visual?
Hi! my main fact table is financial transactions which look pretty much like this: date type amount 10/12/22 revenue 100$ 10/12/22 expense 50$ 01/01/23 revenue 100$ 01/01/23 expense 30$ 05/01/23 revenue 150$ 05/01/23 expense 75$ I want to create a revenue funnel visual that shows the conversion between the revenue and the margins. from what I understand, I need to get a table at this structure ( I can drop the year but I want to keep it for future visualization) year type amount 2022 revenue 100$ 2022 margins (100$-50$) = 50$ 2023 revenue (100$+150$) = 250$ 2023 margins (100$+150$) - (30$+75$) = 145$ Then I will drag the type to the funnel group and the amount to the funnel value. How can I create this table using DAX? Thanks! Tal
Dynamic "Group by" with custom sorting
Hi, I have a table with activities of users. Each record has user name and date of the activity. I need to create clusters of activity counts like this: The grouping needs to be dynamic based on the Date filter selection. In the end I need two visualisation to be able to filter each other: The visualisation Count of Users by # of activities should represent number of users in each cluster in the Date filter context. By clicking on the bar the table List of Users should be filtered on the users in the cluster. By clicking on the user in the table the visualisation should show to which cluster the user belongs. I can't figure out how to connect the visualisations because there is no key how to connect custom Ranges table and the original table with activities. Could someone help? Thanks in advance!
