dynamic index

4 Topics

Cumulative reset after reaching sales target
Hello everyone, I have sales data set containing sales amount of every stores in 3 different regions. Each region has their own sales target (column Target Sales), I want to calculate a cumulative sum of sales amount such that it is resetted once the target is reached or almost reached. I tried using grouping method but do not get 100% desired result. For example, for the South region, the 'Reset cumulative' works perfectly until group 3. Group 1 includes 0.29 + 8.57 + +
SailorMoon98
2 years ago Place DAX Commands and Tips
1KViews
0likes
4Comments
DAX Expression to create dynamic Index column to show all categorical values without scrollbar
Hi Community, Good Evening. I have a requirement to show all the values for a categorical column without a scrollbar. I created my chart with dimensions that had 370+ distinct values and DAX measure and got the below chart. But my business users want to see all the values without scroll bar. To meet my requirements, I need a dynamic DAX column that holds an index value that can be used in the X-Axis as continuous. But how to write DAX to get such a dynamic index column to change with your selections. Dimension: Station Name Measure: Actual Travel Time / Total Time I have separate facts in the model and too many dimensions as filters in the page. Thanks
Solved
datpbi_newbie
4 years ago Place DAX Commands and Tips
1.8KViews
0likes
4Comments
Calculate difference between consecutive rows filtered by SELECT DISTINCT
I need to calculate the difference between values in consecutive rows and display the value in the 2nd of each pair of rows, apparently without the help of an index. Specifically, I created a calculated column, Workdays Since Filed, and now want to subtract the Row 1 Workdays value from the Row 2 value and display the result in Row 2 as a calculated measure. Values in red show how it should work: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED (calculated) DAYS BETWEEN (calc) (Just to show math, not a real column) FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 16 - 11 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 17 - 16 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 20 - 17 Getting the "Days Between" calculation is complicated by the filters on this table that made using an index difficult. The main filter is PERMIT_ID and that could be indexed using dynamic filtering tricks I saw on other posts in this forum, but the filter that really hoses things up is a SELECT DISTINCT that removes duplicate rows from display when multiple new documents are received in the same day. So the user sees this: Behind the scenes, the table actually looks like this: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED DAYS BETWEEN FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 I worked through solutions on about 5 similar posts but didn't find one that fit this issue specifically. Any ideas? Thanks, Allison
Solved
Anonymous
4 years ago Place DAX Commands and Tips
4.1KViews
0likes
2Comments
Dynamic INDEX/RANK creation using DAX
ORIGINAL DATA Company Department Job Level Score C1 D5 Executive Management 0.64 C1 D4 Executive Management 0.17 C1 D2 Executive Management 0.06 C1 D1 Middle Management 0.86 C1 D5 Junior Management 0.64 C1 D4 Junior Management 0.95 C1 D4 Junior Management 0.17 C1 D1 Junior Management 0.37 C1 D5 Executive Management 0.27 C1 D3 Junior Management 0.05 C1 D5 Senior Management 0.97 C1 D3 Senior Management 0.42 C2 D4 Senior Management 0.68 C2 D1 Middle Management 0.68 C2 D2 Junior Management 0.17 C2 D1 Junior Management 0.46 C2 D4 Middle Management 0.24 C2 D4 Junior Management 0.85 C2 D4 Junior Management 0.88 C2 D1 Senior Management 0.50 C2 D3 Executive Management 0.61 C2 D4 Junior Management 0.15 C2 D1 Junior Management 0.65 C2 D1 Senior Management 0.12 C2 D5 Executive Management 0.10 C2 D4 Middle Management 0.52 C3 D4 Junior Management 0.61 C3 D5 Senior Management 0.35 C3 D4 Junior Management 0.73 C3 D2 Junior Management 0.29 C3 D3 Junior Management 0.27 C3 D2 Junior Management 0.97 C3 D4 Executive Management 0.65 C3 D5 Middle Management 0.81 C3 D1 Middle Management 0.39 C3 D2 Junior Management 0.32 C3 D4 Middle Management 0.07 C3 D1 Junior Management 0.96 C3 D4 Middle Management 0.33 C3 D3 Middle Management 0.56 I want to create two index column dynamically which reset itself based on the selection in slicer. Index column 1: This index column is sorted by Job Level and Score & partioned by company and department The data should be sorted in the below specified format which is - 1. Company (alphabetically) 2. Department (alphabetically) 3. Job Level (Executive, Senior, Middle, Junior Management) 4. Score (Decreasing order) Sorting Criteria: 1. If the Job Level is same within a department then sort the index on score. DATA WITH INDEX COLUM 1 Company Department Job Level Score Index Column 1 C1 D1 Middle Management 0.86 1 C1 D1 Junior Management 0.37 2 C1 D2 Executive Management 0.06 1 C1 D3 Senior Management 0.42 1 C1 D3 Junior Management 0.05 2 C1 D4 Executive Management 0.17 1 C1 D4 Junior Management 0.95 2 C1 D4 Junior Management 0.17 3 C1 D5 Executive Management 0.64 1 C1 D5 Executive Management 0.27 2 C1 D5 Senior Management 0.97 3 C1 D5 Junior Management 0.64 4 C2 D1 Senior Management 0.50 3 C2 D1 Senior Management 0.12 4 C2 D1 Middle Management 0.68 5 C2 D1 Junior Management 0.65 6 C2 D1 Junior Management 0.46 7 C2 D2 Junior Management 0.17 2 C2 D3 Executive Management 0.61 3 C2 D4 Senior Management 0.68 4 C2 D4 Middle Management 0.52 5 C2 D4 Middle Management 0.24 6 C2 D4 Junior Management 0.88 7 C2 D4 Junior Management 0.85 8 C2 D4 Junior Management 0.15 9 C2 D5 Executive Management 0.10 5 C3 D1 Middle Management 0.39 8 C3 D1 Junior Management 0.96 9 C3 D2 Junior Management 0.97 3 C3 D2 Junior Management 0.32 4 C3 D2 Junior Management 0.29 5 C3 D3 Middle Management 0.56 4 C3 D3 Junior Management 0.27 5 C3 D4 Executive Management 0.65 10 C3 D4 Middle Management 0.33 11 C3 D4 Middle Management 0.07 12 C3 D4 Junior Management 0.73 13 C3 D4 Junior Management 0.61 14 C3 D5 Senior Management 0.35 6 C3 D5 Middle Management 0.81 7 Index column 2: This column will be created after sorting the data by company, index column 1 and job level and partitioned by company. DATA WITH INDEX COLUMN 2 Company Department Job Level Score Index Column 1 Index Column 2 C1 D2 Executive Management 0.06 1 1 C1 D4 Executive Management 0.17 1 2 C1 D5 Executive Management 0.64 1 3 C1 D3 Senior Management 0.42 1 4 C1 D1 Middle Management 0.86 1 5 C1 D5 Executive Management 0.27 2 6 C1 D1 Junior Management 0.37 2 7 C1 D3 Junior Management 0.05 2 8 C1 D4 Junior Management 0.95 2 9 C1 D5 Senior Management 0.97 3 10 C1 D4 Junior Management 0.17 3 11 C1 D5 Junior Management 0.64 4 12 C2 D2 Junior Management 0.17 2 1 C2 D3 Executive Management 0.61 3 2 C2 D1 Senior Management 0.50 3 3 C2 D1 Senior Management 0.12 4 4 C2 D4 Senior Management 0.68 4 5 C2 D5 Executive Management 0.10 5 6 C2 D1 Middle Management 0.68 5 7 C2 D4 Middle Management 0.52 5 8 C2 D4 Middle Management 0.24 6 9 C2 D1 Junior Management 0.65 6 10 C2 D1 Junior Management 0.46 7 11 C2 D4 Junior Management 0.88 7 12 C2 D4 Junior Management 0.85 8 13 C2 D4 Junior Management 0.15 9 14 C3 D2 Junior Management 0.97 3 1 C3 D3 Middle Management 0.56 4 2 C3 D2 Junior Management 0.32 4 3 C3 D2 Junior Management 0.29 5 4 C3 D3 Junior Management 0.27 5 5 C3 D5 Senior Management 0.35 6 6 C3 D5 Middle Management 0.81 7 7 C3 D1 Middle Management 0.39 8 8 C3 D1 Junior Management 0.96 9 9 C3 D4 Executive Management 0.65 10 10 C3 D4 Middle Management 0.33 11 11 C3 D4 Middle Management 0.07 12 12 C3 D4 Junior Management 0.73 13 13 C3 D4 Junior Management 0.61 14 14 Please remeber i need a measure which changes on the basis of slicer. There are 14 more filters to slice and dice the data so calculated column won't work. I have created a SQL query to understand the desired outcome Select distinct JOB_LEVEL, (case when JOB_LEVEL = 'executive management' then 1 when JOB_LEVEL = 'senior management' then 2 when JOB_LEVEL = 'middle management' then 3 when JOB_LEVEL = 'junior management' then 4 when JOB_LEVEL = '' then 0 end ) JOBLEVELCODE into #temp FROM DimJobLevel select company ,department ,job_level ,score ,index_column_1 ,ROW_NUMBER() over(partition by company order by company) as index_column_2 FROM ( Select company ,department ,t1.job_level ,t2.JOBLEVELCODE ,score ,ROW_NUMBER() over(partition by company, department order by company, t2.JOBLEVELCODE) as index_column_1 FROM FactTbl t1 left join #temp t2 on t1.job_level = t2.JOB_LEVEL order by company ,department ,t2.JOBLEVELCODE ,score desc ) a order by company, index_column_1, JOBLEVELCODE
Solved
satindenave
6 years ago Place DAX Commands and Tips
1.7KViews
0likes
3Comments