@

5 Topics

Filtering a table using TOP N slicer
Hi, i have a TOP N slicer which is a what if parameter from 0 to 50, i am trying to filter TOP N qty for a product in a table. please note there is already a TOPN filter on week for the table visual like pic below, so another topn is not possible and the data does not have an id/unique column and the products are repeted several times and the week in the sample is different from the topn loaddateweek. I am using a DAX measure to calculate the top products TOP N measure = VAR SelectedTop = SELECTEDVALUE('TOPN'[TOPN]) RETURN SWITCH(TRUE(), SelectedTop = 0, [Still to supply_sum], RANKX( ALLSELECTED(table[week],table[Product]), [Still to supply_sum], ,DESC,Dense ) <= SelectedTop, [Still to supply_sum] ) but this is not filtering for only 1 value when top n filter selected =1, but shows multiple value. Sample data is like below: Week Product Still to supply_sum rank 2024 34 6708 58720 1 2024 40 7706 64919 1 2024 38 6708 48120 2 2024 40 6708 43040 2 2024 38 731 34400 3 2024 36 6708 33560 3 2024 39 7448 36000 3 2024 41 6961 32040 5 2024 38 300 30080 6 2024 36 300 28120 7 2024 30 7551 24720 9 2024 41 122116010 22160 10 2024 36 731 21560 11 2024 41 7551 19790 15 2024 32 7551 19168 17 2024 31 300 16204 24 2024 32 300 15669 25 Not sure how to solve this issue to just have rank that does not repeat for a another row for the same product. Thanks for the help in advance.
Solved
Bu__
1 year ago Place DAX Commands and Tips
1.3KViews
0likes
3Comments
LINESTX with slicer
Hello, I am trying to calculate trends using slope and intercept I am using the following DAX, which is functioning well. However, the issue arises when I have a slicer on the order date. I want the dataset for the query below to also get filtered for that date range when a user applies a start and end date filter on the order date slicer Revenue trend = VAR line = LINESTX ( ALL('Sales order line fact'[Order date] ), [Total revenue trend], 'Sales order line fact'[Order date] ) VAR slope = SELECTCOLUMNS ( line, [Slope1] ) VAR intercept = SELECTCOLUMNS ( line, [Intercept] ) VAR x = SELECTEDVALUE ( 'Sales order line fact'[Order date] ) VAR y = x * slope + intercept RETURN y
Solved
Ethanhunt123
1 year ago Place DAX Commands and Tips
1.8KViews
0likes
4Comments
Hi all ,i have a requirement below and getting argument 4 lookup value required
if (custoemr is in A table AND if the BU type is not "UT", "DT", "PT") or if customer number = 5150 then = 'In' else 'OUT' INOUT= IF ( OR ( AND ( NOT ISBLANK ( LOOKUPVALUE ( 'ASP vw_A'[businessUnitName], 'ASP vw_A'[businessUnitNumber], 'ASP vw_B'[CustomerNumber] ) ), NOT OR ( SELECTEDVALUE('ASP vw_A'[BU Type]) IN {"UT", "DT", "PT"} ) ), 'ASP vw_B'[CustomerNumber] = 5150 ), "IN", "OUT" )
Solved
rupa1006
2 years ago Place DAX Commands and Tips
699Views
0likes
1Comment
Dax
how do i take difference from last month?
Anonymous
3 years ago Place DAX Commands and Tips
373Views
0likes
1Comment
Measure for average processing time with data slicers
Hello everybody! I have the following scenario: I have a table with order IDs. This table also contains the creation date of the orders and the current check date (every seven days). There are also two columns for orders in process and finished orders. That means on every check date (every seven days) a new row is created for each order, where the current status of the order (In process or finished) is newly evaluated and marked in the corresponding column. This is the table with example data: OrderId CreatedAt CheckDate InProgress Finished 1 11.01.2021 25.01.2021 X 2 18.01.2021 25.01.2021 3 18.01.2021 25.01.2021 4 25.01.2021 25.01.2021 1 11.01.2021 01.02.2021 X 2 18.01.2021 01.02.2021 X 3 18.01.2021 01.02.2021 4 25.01.2021 01.02.2021 X 5 01.02.2021 01.02.2021 X 6 01.02.2021 01.02.2021 1 11.01.2021 08.02.2021 X 2 18.01.2021 08.02.2021 X 3 18.01.2021 08.02.2021 4 25.01.2021 08.02.2021 X 5 01.02.2021 08.02.2021 X 6 01.02.2021 08.02.2021 X 7 08.02.2021 08.02.2021 Now I want to create a measure that always calculates the average processing time for a selected check date. This is my current calculation for it: Average Processing Time (in days) = var _processingTime = CALCULATE( ((COUNTROWS(Data)*FIRSTDATE(Data[CheckDate])) - SUM(Data[CreatedAt]))/COUNTROWS(Data), FILTER(KEEPFILTERS(Data), Data[CheckDate]=SELECTEDVALUE(Data[CheckDate])), FILTER(KEEPFILTERS(Data), Data[InProgress]<>BLANK() ||Data[Finished]<>BLANK()) ) return _processingTime The problem is that my Measure does not use the check date when an order was first given the status "In Progress" or "Finished". Instead, it always uses the check date selected by the filter. For example, in the screenshot the measure shows a result of 15.40 days. But the correct solution should be 8.4 days as is shown here, which I need for my report: Correct Average Processing Time for CheckDate=08.02.2021: Id CreatedAt Processed first at CheckDate Days 1 11.01.2021 25.01.2021 14 2 18.01.2021 01.02.2021 14 4 25.01.2021 01.02.2021 7 5 01.02.2021 01.02.2021 0 6 01.02.2021 08.02.2021 7 Calculation (14 + 14 + 7 + 0 + 7) / 5 = 8,4 And this is the current wrong calculation of my measure: Current wrong calculation of Average Processing Time for CheckDate=08.02.2021: Id CreatedAt last CheckDate Days 1 11.01.2021 08.02.2021 28 2 18.01.2021 08.02.2021 21 4 25.01.2021 08.02.2021 14 5 01.02.2021 08.02.2021 7 6 01.02.2021 08.02.2021 7 Calculation (28 + 21 + 14 + 7 + 7) / 5 = 15,4 Does anybody have an idea how to rewrite this measure to get the correct average processing time, even when I filter my data with the data slicers as shown in the screenshot? Hope you guys can help me out! Thank you and kind regards! JMPowerBI
Solved
Anonymous
5 years ago Place DAX Commands and Tips
1.9KViews
0likes
2Comments