dax

  1. S

    Need help DAX LOOKUP formula which would work like VLOOKUP

    Hello Everyone, I have two tables Table 1:- Voucher No. Invoice No. Invoice Amount Invoice Date Vendor Name 1 ABC-1 100 14/05/2019 ABC Company 1 ABC-2 200 14/05/2019 ABC Company 2 EFG-1 300 31/05/2019 EFG Company 2 EFG-2 400 31/05/2019 EFG Company 3 XYZ-1 500 30/06/2019 XYZ...
  2. H

    Drill through overwrite the filters inside calculate

    Hello MrExcel members. I made a cumulative measure using calculate, all calendar and max date, very easy basic DAX. When I drill through from page to page in Power BI desktop report this cumulative measure doesn't work, more specific All Calendar doesn't work, because I tried using All Data...
  3. D

    Dax - DATESMTD

    Hi I have a model of transactional data, which does include future dates, and i am having issues with DATESMTD, in the fact it appears to be picking the future dated transactions as the basis of calculating the MTD values I have the following base measure (works fine): Net Expenditure Total...
  4. K

    Display on pivot table a count of only those column values which has a unique ID (in a separate column)

    Explanation of my source table: I have column E which has data-validation dropdowns for Reviewer numbers, and column R which has traffic transaction details. Reviewer now check each of the transaction (Column R), and once review is done, will click on the dropdown (Column E) and select Reviewer#...
  5. J

    PowerQuery - Pass Parameter from Cell Value replacing Variable from T-SQL Script

    Hi All, I am new to Power Query and I'm seeking some assistance. I have a script that I have created within SQL. Within the script, I have DECLARE'd and SET a variable for a 'Report Date'. This variable is used throughout the query and various date fields use this date. Previously, when I...
  6. S

    Need help of Date Format

    Hello Everyone, Is there a way to show a date as "MM-YYYY" format in power query or in DAX. The actual data is in "DD/MM/YYYY" format. Thanks in advance. Best Regards,
  7. G

    PowerPivot CALCULATE SUM

    Hi, would appreciate any help with this, I'm trying to do a calculate sum if in PowerPivot, example of how it workls in Excel below, but am unsure how to replicate in PowerPivot. Thanks in advance for any help. Gav.
  8. E

    DAX Measure to Calculate and Filter Two Date Columns

    Hello, I was hoping someone would be able to help, I am looking to incorporate a measure for a cube value formula in excel where the date input is dynamic based on where the formula is. Essentially I am trying to use the data below to say based on the date selection in excel (lets use...
  9. F

    DAX - Finding specific value/text within Column

    I am trying to figure out the correct measure and/or column DAX operation to extract whether another column has a specific string of characters. See example below as to what results I am attempting to achieve. Example: Column Data Results Testing 1.0 for Deployment 1.0 Testing 2.0 for...
  10. B

    How to make PB query as a JSON and send to remote server via REST?

    Hello there, I am querying a remote PB server by doing a POST to its REST url (something like https://XYZ/public/reports/querydata). The query is contained in a JSON file which looks like this { "queries" : [ { "ApplicationContext" : { "Sources" : [ {...
  11. L

    Filtering a Calculated Measure From Another Table

    Hi All, My brain is fried again. I have scenario where I have to calculate various stats between two sets of data. The only thing common between the two data sets are the Calendar Weeks and the Supplier Numbers. So I have two tables: Table 1 has all of the supplier claims quantities by...
  12. J

    RANKX hierarchy with slicer

    Howdy, I don't use PowerBI but I just started using the "add to data model" option in a pivottable and I am fiddling with DAX equations. I wrote a RANKX formula that works great...except it shows the rank for total when I filter for brand. Total is just a column that has 'Total' for a value...
  13. E

    CUBEVALUE returning blanks?!?!

    Hey all, I have tried to the following CUBE formulas in order to get a value based on results from two columns within a dataset inside Power Pivot, however they all return blank. =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Path PP Plan Yes]", "[Path].[Pathway Plan].No", "[Service...
  14. C

    creating percentile bins at weekly level for daily data

    hello BI experts, have started to play around with power pivot and power BI over the last few days. Particular case here is to create bins (measure or column, not sure!) that reflect the weekly deciles for data which is given at a daily level. There is a lot of literature written on percentilex...
  15. K

    RELATED function send me back empty colum

    Hi Guy, This is my first time here, I would like you to help me concerning problem. Indeed I have got a cardinality One to One between two datas tables, until now everything is ok ; but when I wrote a RELATED function (Which is the same as VLOOKUP in Excel) all my entire colum is empty. If you...
  16. M

    DAX Formula for last corresponding text value in Max Date

    I am using Excel 2016 and have a very similar problem to the question posted in this previous thread: DAX Formula for last corresponding value in Max Date The solution given in the comments of that post work perfectly if the values I'm trying to show are numbers, but they don't work if they...
  17. O

    DAX - Find max value based on another column being filtered

    Hello, I have a column of financial values and I want to find the MAX value of that column when a certain value appears in another column. I tried this but without any luck. CALCULATE( MAX('IntCustomSearchResults'[Value of Incident]...
  18. S

    Extract the values from a table based on a rule and create a new table

    Hi I am quite new to power bi and trying to learn how to use DAX and other power bi queries. I have been trying but not able to get what I want from my below query. I hope someone can help. I have same data in below 2 formats and I want the output table as below. Any data table can be used to...
  19. S

    DAX = Index and match question

    Hi, I have 2 tables: SalesGOALSAppend, has LOCATION and SALES GOAL and DATES EWCHoursBudget, has LOCATION and SALES GOAL and HOURS BUDGET I would like SalesGOALSAppend to look up in what the EWCHoursBudget are when SALES hit a number. So if sales at 9100 in SalesGOALSAppend hit $100,000 look...
  20. C

    Is there a way to find out which days my products changed there content?

    I have tried to figure out which product has had it's content changed and on which date it changed. So far I created to column for the length of the content column because if that changes, the content has changed. And as there are definers for the product such as "ItemNum", "CountryCode" and...

Some videos you may like

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top