dax formulas

  1. S

    How to Compare two filtered tables derived from same table

    Hello, I have the Dax code below. All works fine except the code after RETURN. I am working with Dax Studio. I want to return list of Customer IDs in 2014 that are not in other years. Kindly help resolve. Thanks EVALUATE VAR main_table = SELECTCOLUMNS(Orders,"Order Dates",Orders[Order...
  2. T

    How do you do a RANKX Dax calculation properly??

    I am trying to build out a DAX RANKX formula that will rank Employee ID by Profit. Here is the formula I have today see below... However, this formula messes up when I start to layer in multiple dimensions into rows. Rank - DO Profit by Employee:=IF(ISBLANK([DO...
  3. O

    Employee turnover calc

    I am trying to find Employee turnover by Year/Company I have and employee table and I have created a Calendar table The employee table has the following columns ID Hire Date Term Date Company Active Status I would like to createa dashboard to show by Company turnover % by Year (2018 &...
  4. W

    Power BI: Missing Relationship or DAX query needed?

    Hello everyone! Hope you can help me on this Here's the problem: I'm trying to automate wih power BI something that takes me 4 hours to do. In a first source I get the following information (Excell file) * Reference number * Reference date * Customer * Country * Duration in minutes * Number of...
  5. P

    DAX Formulas (wrong grand totals and missing values)

    Here is picture of needed data. There is a piece of data (table 1). Primary data starts with March 2010 and I filtered it by Employee 1 and Employee 100. As you can see Employee 1 was not at the Project in May and December, so in May Total Expenses change was -$700. One part of my task is to...
  6. Ombir

    How this Dax is working ?

    Hi, I don't have experience with DAX and I'm trying to understand a DAX which is used in a workbook. Here is the link to workbook. https://community.tableau.com/servlet/JiveServlet/download/847360-289331/SampleData.xlsx This is the formula which is used to calculate earned exposure. =SUMX...
  7. M

    DAX code to track project progress as at reporting date

    Hi all, First up, I have posted this query on another forum https://powerpivotforum.com.au/viewtopic.php?f=6&t=1305 I am working on a dashboard that can track all the projects that are currently being undertaken. The aim is to report on the progress of the various projects over a period of...
  8. M

    Subtotals on aggregate conditional values

    I can't tell if the image is loaded. Here's the url: https://imagebin.ca/v/4DD95zPeoS5p Anyway, sum is sum(Table1[value]) maxmo is max(xmonth) measure 1 is if([maxmo]<12,divide([sum]*12,[maxmo],[sum]) How do I get the subtotal at line 1 to equal 48.8 and not 44. I understand why its 44, but I...
  9. Z

    Filter in specific order

    I am truing to use filter in specific order, first filter out all sub accounts to 100 and than find max year_dt, can someone please help me with the expression? Thanks Z Calculations:=CALCULATE( SUM(Inv2018T[Custom]), FILTER(...
  10. F

    Percentile 80 in Dax (Excel 2013 Version)

    I have this sort of table below and I'd like to calculate percentile 80 to "qtd/cons" for each category in "Col1" as a calculated column. Col1 qtd cons qtd/cons <tbody style="border: 0px...
  11. P

    Excel's MAX & MIN function functionality in DAX

    Hi I have a measure to calculate % Growth, but I find I'm getting some large results. I would like to limit the result to be between -999% and +999% in my visuals as these tend to be outliers. In Excel I can do this using a combination of MIN and MAX (eg =MIN(999, MAX(-999, calculation))...
  12. S

    LOOKUP Select Column/Field?

    In Excel I can perform a lookup that uses the desired column for the return value by varying the column in the formula. How can this be done on either the Query side or the PowerPivot side of PowerBI?
  13. T

    DAX Formula Error in PowerPivot

    I was trying to create a DAX formula to show only the value of a particular line, however that number repeats all the way down. See image attached below. I have a calculate formula and have defined the parameters, but that number get repeated everywhere... I only want it to appear once in the...
  14. X

    Average of a distinct count based on criteria

    Hi, I'm new to power pivots and dax formulas. Does anyone know how I can work out the average value of distinct count based on a criteria? Let's assume I have the tables below Customer Table Customer - Region a - AA b - AA c - BB d - CC Product Table Product - Type 1 - X 2 - X 3 - Y 4 - Z...
  15. V

    DAX: Default Member

    All, We have 3 different kinds of M2 calculations: Bruto, Netto and SuperNetto. To make the calculation intelligent, we made one M2 calculation which uses the Netto M2 as a default. In parallel we have a member (Calculation Type) which contain all the M2 kinds (Bruto, Netto and SuperNetto)...
  16. S

    How do i get POWERPIVOT data for the same days last year?

    Hi I'm look to get the MTD sales for the same days last year. IE May 1-8 of 2016 vs. May 1-8 2017. Or in a perfect world May2-9, 2016 vs. May 1-8, 2017 This gives me all of May last year: 1 YR AGO SALES NET RETAIL:=CALCULATE('tblInvSkuMaster'[SALES TOTAL NET...
  17. A

    apply function for specific element DAX

    Hello!, Please I ask fro support in applying function for specific element DAX. I Have a column called LOB, contains 11 elements "Criteria" <tbody> LOB Business Golden Customers NC Reapers TEData-Sales TEData-Sales "Subscription" WE Xceed-UP Q Xceed-Basic Xceed-Sales...
  18. B

    Dealing with (multiple) many2many relationships in Powerpivot

    I have a question about how to deal with multiple many2many relationships in Powerpivot. Here is my scenario: I have a bunch of products, where each product is assigned to at least one but potentially more categories, so for example my first table is Product Category P1 C1 P1 C2...
  19. T

    DAX IF Statement not working

    I have a data model built in Power Pivot with a few tables connected and I am trying to do a simple formula based on a few conditions. Basically calculate this formula if Scenario 1, 2, and 3 are true. Below is a view of the Error: The way it is written is like a nested IF Statement, but I am...
  20. J

    DAX code to count orders with crossed categories

    I've got a list of units which are being sold by a shop. Each line describes date, order number, number of units and category (A and B in this example). How can I, using just DAX measures in a pivot table, know how many orders are both in categories A and B per day? I've tried using CALCULATE in...

Some videos you may like

This Week's Hot Topics