Questions tagged [dax]

Expression language used in Microsoft Power Pivot, SQL Server Analysis Services Tabular, and Power BI for performing analytical calculations.

0
votes
1answer
20 views

Fixing Dynamic return text of a PowerBI / DAX calculation, error says 'table of mutiple values supplied… where single value was expected'

I'm creating a PowerBI dashboard for supply chain backorders. There was a great video I saw from Microsoft demonstrating a Dynamic message card on their Destiny dashboard showing players and their ...
1
vote
0answers
17 views

Formula to be able to Select specific Criteria to return a multiplication value for the blank datss

I am trying to further develop a formula which has condition as below : Choose the desired ID, it will look at the Volume value of the Products of that ID and it will multiply that volume value into ...
1
vote
1answer
35 views

Calculate row difference in Power Bi

I am looking for help calculating the difference between the previous row, and the % after that in order to built a funnel in Power Bi. Could you help me?
0
votes
1answer
31 views

Calculated Column --A Table of Multiple Values was supplied using DAX IN POWERBI -

I have two tables which have two columns some with similar data but I want lookup values from one of the table when the data in the two columns match and if it does not match it returns data in the ...
-1
votes
2answers
32 views

DAX Calculated column to consider multiple rows to generate a result

I have a dataset like this. Reference_ID MyCode 1 NULL 1 S1010 1 NULL 1 1011 2 ...
0
votes
2answers
23 views

Power BI dividing function shows negative value instead of positive

I have a problem and probably it is very simple. There are sales values of 2 years for some markets in my data. I'm trying to calculate annual growths so I divide 2019 value of a market by 2018 value ...
0
votes
0answers
19 views

Monthly and daily insights on the same page in Power BI

I am working on a report which is expected to provide both daily and monthly insights on the same page. I have set a page level filter on the current date.So all my visuals are filtered for current ...
3
votes
2answers
62 views

PowerBI Natural Left outer join issues with deleted rows in right table

I have two tables. 1) Table 1 : 1 column with date value 2) Table 2 : 2 columns : Date column + business value column I am trying to use DAX in PowerBI to create a new table using a left outer join ...
0
votes
0answers
22 views

Reference a Column Header in a DAX Formula

In a PowerPivot calculated column, I'm attempting to LookupValue the hours in a month from a separate table, using the value in the previous column's header as my search_value. Is this possible? I'...
0
votes
1answer
19 views

Nested IF function for OR logic

I cannot seem to see what I am doing wrong with the following nested IF formula... internet search IF ( SEARCH ( "compontentdissociation", 'njrew_k_prmry_bicon_outcm'[...
1
vote
0answers
28 views

How to convert string column to date in ssas tabular model

I have created two calculated columns to convert date format as DD-MMM-YYYY(Ex:- 01-Jan-2018). If I access cube data in excel those columns converting as string type. I would like to convert them as ...
0
votes
1answer
25 views

filtering measures based on two columns in power bi dax

I want to use a measure and filter the result based on the columns: My measure is : TotalProductionCon = SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[SGWCP8] ) + SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[...
0
votes
0answers
13 views

“Summarizing over items, count the results and putting it on a bar chart”

For my challenge I need to count how many times a ship enters a port and how many time a ship leaves a port. I assign +1 each time he enters and -1 each time he leaves. If all movement get registered ...
-1
votes
0answers
15 views

How to write a DAX formula to compare selections within the same table

I want to write a measure in PowerBI (DAX) to compare data within the same table. I am looking for a DAX formula that checks whether a ContactID has an AppointmentType A and an AppointmentType B. The ...
0
votes
0answers
29 views

Using Switch function to handle the values in an expanded Matrix visual in Power BI?

So I have been able to change the formula in a matrix using switch when navigating the separate hierarchies in a matrix visual in Power BI. However, I'm looking to have alternate formulas in an ...
2
votes
0answers
29 views

Is it possible to hide the (All) Level for Attribute Hierarchies in SSAS Tabular model

In SSAS multidimensional model the (All) level is an optional level of hierarchy and it is a system-generated level. It contains only one member whose value is the aggregation of the values of all ...
0
votes
0answers
18 views

How to get bar chart to highlight bar when element is selected in filter/slicer

[POWER BI] Hi, I want the bar in a bar chart to highligt when the value is selected in the slicer. Hence, i want all values to show and highlight the selected value. Desired Result I created a ...
0
votes
0answers
20 views

DAX CONTAINSTRING with column

I have two tables, first sheet1 with columns Operating System, Site,Count and second with Products Released,Extended Support End Date. I want view Extended Support End Date in my first table. I ...
1
vote
1answer
17 views

Using RLS on secondary field to UserPrincipalName

So if I have three users: Tom, Dick, and Harry, and they have been assigned group colors. I'd like them to be able to see everyone's data in their own group. Name Group Email ----- ----- ---...
0
votes
0answers
44 views

How to count the number of occurrences in another table with condition in power BI?

I'm trying to implement in Power BI what I did in an Excel file but I am facing some issues. I would like to manage calculations in Power BI in order to avoid calculation in Excel. So, I have 3 ...
1
vote
1answer
44 views

How to re-use the filters used in CALCULATE function when adding columns

I'm trying to add some columns in DAX using CALCULATE functions, just like this: ADDCOLUMNS( Summarize( CALCULATETABLE( 'MY TABLE' 'MY TABLE'[Year] = 2019, ...
-1
votes
1answer
18 views

DAX Query to build current quarter numbers and same quarter last year’s numbers for snapshot data

I’m currently dealing with sales pipeline analysis where weekly snaps are taken for the data to see how has pipeline progressed. Snap date is nothing but the date when weekly snap was taken. I would ...
-1
votes
0answers
26 views

Need some help in dax to implement business logic

I have few columns in a table related to crops and fields and need to determine the number of cuts a crop has undergone. Below are the columns I have and need to determine the number of cuts the crop ...
1
vote
1answer
53 views

Distinct Count of a separate Running Total measure

I'm trying to determine a distinct count of customer id from an overall running total of outstanding service requests. My table contains these columns: RequestID, CustomerID, OpenDate, ResolvedDate ...
0
votes
0answers
22 views

How to add a column with a filter in power bi

I currently have the following table: then I find the ranking, which will vary with a filter. The rankig I calculate is like this: first I create the column uniqueRank, which will have for tiebrakes ...
1
vote
0answers
65 views

Sum with filter on DAX/PowerBI

I need to sum each month the ID's amounts that meet the next criterias: Date End < Agreement date Date End < Month to show I have tried with filter, sum and others options, but the result is ...
0
votes
1answer
38 views

Split row in multiple other rows in Power Bi based on a division of a number

In Power BI Desktop i have a table from an excel file and i want to split a row based on a division between the value of a specific column and a default number. In more details lets assume tha we ...
0
votes
1answer
35 views

Dynamically rank a column based on filters

In my Power BI I have the following table What I want is that when you create a graph and have a filter, the ranking column is modified. An example would be that when I change the ranking and select ...
1
vote
2answers
37 views

Add calculated column

I'd like simply to add a calculated column to a pivot table that i got. This pivot table uses two sources of data. Each source provides one column to the pivot table. I wish I could divide one column ...
0
votes
0answers
18 views

Is there a way to manipulate with KPI report x and y lines reflected by slicers

Looking for help with the below: I have 3 tables I would like to create KPI Sales report according to date slicer and retailer. For example, when selected '6/20/2017' X line on KPI must show months ...
0
votes
1answer
13 views

Merge Custom (Manual Entry) Column onto New Table with DAX

I am trying to create a new table in PowerBI Desktop where the initial table is created using DAX, ie. New Table = Distinct(Roster[Name]) to source the most up to date list of users, and then would ...
0
votes
0answers
17 views

How to use parameter name instead of USERNAME()

Is any way to use parameters instead of USERNAME() in RLS into Power BI ? Like - [Column Name] = <{Parameter Name}>
0
votes
1answer
19 views

power bi column count of rows based on string

I'm trying to achieve something simple, but I can't get my head around it. I have a table with many rows and in one particular column, rows have the same value. I would need an additional column to ...
0
votes
3answers
44 views

How to convert ifnull statement in tableau to powerbi dax?

I'm working on a dashboard conversion from tableau to power bi.Stuck with a calculated dimension in tableau that is needed to be converted to powerbi using dax! By Replacing the IFNULL statement in ...
1
vote
1answer
32 views

Power BI DAX to split the value in to multiple ranges

I have the data as below. Product Channel Account_Id Max_tier Balance 2000-0100 Direct 102123625 4999.999 174589.26 2000-0100 Direct 102123625 9999.999 174589.26 ...
0
votes
1answer
22 views

Using Avg Measure When Specific Measure Unavailable

I have a situation where in 99% of a list of retail items, calculating Cost of Goods Sold is a simple measure: COGS:= SUM([Units Sold]) * [Unit Purch Cost] However, I have a few items that were ...
0
votes
0answers
18 views

Reverse Iterative DSO DAX Calculation

A member of our executive team wants to try a Daily Sales Outstanding (DSO) calculation that is a bit different than the standard of "balance amount / invoice amount * days". He wants to see DSO ...
-2
votes
0answers
14 views

How do I calculate total calls in a period using dax

I am trying to calculate the average # of calls to set an appointment using a rolling 90 days in Power BI
2
votes
2answers
34 views

DAX - How to sum the most recent values by group?

I have 2 tables, the first is a sanitised version of the data, the second is what I need to calculate. AnimalCount Animal DATE Count Lion 2019-01-01 2 Cat 2019-01-01 45 Cat 2019-...
0
votes
1answer
36 views

how to get all the records in between a Start Date and End date in Power BI?

am a newbie to PowerBi i need some help to filter all the values which come in between the start date and end date. when i tried only one of the filter is working and both the start date and end date ...
0
votes
1answer
28 views

Power BI Bar Chart

I have table of customers that has these data CustomerID DemandHour PurchaseHour 1001 15 19 1002 03 07 1003 19 07 1004 23 ...
2
votes
1answer
37 views

dax for events in progress at end of period using date table

I need to sum up total dollars of all open opportunities by date. Each record has a [valid from] and a [valid to] date attached to it. When an opportunity gets updated in the DB, a new line is added ...
1
vote
1answer
22 views

DAX - Return Latest Date that is Before Posting Date

I have three tables. Purchase Invoice, Tax Area, Tax Jurisdiction. Sample data is in image. Each Purchase Invoice record contains a [Tax Area]. Each [Tax Area] is made up of several [Tax Jurisdiction ...
0
votes
1answer
22 views

ISLEAF and IIF MDX to DAX

I am currently working on migration of SSAS cube from Multidimensional to Tabular. I have many MDX queries. For example: IIF(IsLeaf([Currency].[Currency]),[Measures].[_Discount],null) I was trying ...
0
votes
1answer
29 views

How I can find percentage out of subtotals with multiple columns in Pivot table

How I can find percentage out of subtotals with multiple columns in Pivot table. PercentYes := CALCULATE ( SUM ( MyTable[value] ), MyTable[answers] = "yes" ) / CALCULATE ( SUM ( ...
0
votes
1answer
20 views

Calculate percentage of a False boolean in a column in Power BI

I have a boolean column for my data and I need to calculate what percentage is False
0
votes
1answer
15 views

Calculated column showing Circular Dependency when there isn't. How to fix?

Table 1: "Stocks History" - I have some historical daily level Stock Market data for some companies/equities like Stock Name, Symbol, Closing Price for the day etc. Table 2: "Market Cap" - In another ...
0
votes
1answer
36 views

How to get the first value from a sorted table into a measure?

I have a DAX table with 2 columns RANK and VALUE. I need a measure that returns the VALUE for the row where RANK is the lowest. Solution needs to be efficient as table has tens of millions of rows ...
0
votes
0answers
18 views

YTD calculation with DATESBETWEEN introduces “blank” into grouping labels

I'm trying to do some YTD calculations in one of my summaries. I did it in 2 different ways, each came with its own problem: At first, I tried using the TotalYTD function: YTD Sales Alt = ...
0
votes
1answer
29 views

Power BI - find the sum of fields if other fields are equal

I am trying to get the aging of objects over time, per "status". One of the status is "On Hold" any individual object can be on hold multiple times. (1 to many table relation in the DB). I have merged ...