Dax Running total measure incorrect value

[Anonymous]

Hi,

I have table columns sublock,elementpath, value(integer). sublock and elementpath forms unique key.

input slicer sublock has values 1a,1b,1c to 1s, 2a,2b,2c to 2s, 4a,4b,4c to 4s

i created running total measure (RT_M)for input column value.

if user selects sublock slicer 4F. then report visual starts from 4F.i created distance measure from input slicer it will generate distance values(ex: 4f 0, 4g 1,4h 2) so i can sort visual by distance.

In the below screen shot we can see that running total (RT_M) is not calculated for first few rows.D_I_M is a measure to calculate distance. please help for RT_M

RT_M =

var currentkey = [Uniquekey_M]

 RETURN CALCULATE(SUM('EEP vwMWValue'[Value]),FILTER(ALL('EEP vwMWValue'), 'EEP vwMWValue'[Uniquekey_M] <= currentkey))

D_I_M =

VAR MaxValue = MAXX(ALL('EEP BlockSubBlock'),'EEP BlockSubBlock'[ID])

VAR SlicerValue = SELECTEDVALUE('EEP BlockSubBlock'[ID],MIN('CBI vwPBIData'[StartID]))

RETURN

    IF(min('EEP vwMWValue'[ID]) >= SlicerValue,

       min('EEP vwMWValue'[ID]) - SlicerValue,

       min('EEP vwMWValue'[ID])  +(MaxValue- SlicerValue)

    )

 below is sample data.

SubBlock	Sum of Value	RT_M	D_I_M	Uniquekey_M	ElementPath
14 S	3.06		0	000-14 S\AOR 1\HUMBOLDT\UKIAH\BANK 1\CB 1115	\AOR 1\HUMBOLDT\UKIAH\BANK 1\CB 1115
14 S			0	000-14 S\AOR 3\EAST BAY\BERKELEY T\BANK 1\CB 401	\AOR 3\EAST BAY\BERKELEY T\BANK 1\CB 401
14 S			0	000-14 S\AOR 3\EAST BAY\BERKELEY T\BANK 1\CB 404	\AOR 3\EAST BAY\BERKELEY T\BANK 1\CB 404
14 S			0	000-14 S\AOR 4\SAN FRANCISCO\SF E\BANK 1\CB 407	\AOR 4\SAN FRANCISCO\SF E\BANK 1\CB 407
14 S	5.92		0	000-14 S\AOR 6\CENTRAL COAST\SPENCE\BANK 1\CB 1102	\AOR 6\CENTRAL COAST\SPENCE\BANK 1\CB 1102
14 S	2.71		0	000-14 S\AOR 7\STOCKTON\LODI\Bank 2\CB 1101	\AOR 7\STOCKTON\LODI\Bank 2\CB 1101
1 A	4.03	4.03	1	001-1 A\AOR 3\NORTH BAY\SAUSALITO\BANK 2\CB 1101	\AOR 3\NORTH BAY\SAUSALITO\BANK 2\CB 1101
1 A	2.6	6.63	1	001-1 A\AOR 4\SAN FRANCISCO\MISSION (SF X)\12kv Bus Section E\CB 1107	\AOR 4\SAN FRANCISCO\MISSION (SF X)\12kv Bus Section E\CB 1107
1 A	2.560547	9.190547	1	001-1 A\AOR 7\YOSEMITE\CHOWCHILLA\BANK 2\CB 1105	\AOR 7\YOSEMITE\CHOWCHILLA\BANK 2\CB 1105
1 A	4.032234	13.22278	1	001-1 A\AOR 8\KERN\TEVIS\BANK 1\CB 2101	\AOR 8\KERN\TEVIS\BANK 1\CB 2101
1 B	0.85	14.07278	2	002-1 B\AOR 1\NORTH VALLEY\CLARK ROAD\BANK 1\CB 1101	\AOR 1\NORTH VALLEY\CLARK ROAD\BANK 1\CB 1101
1 B	0.880188	14.95297	2	002-1 B\AOR 2\SIERRA\SUMMIT\BANK 1\CB 1102	\AOR 2\SIERRA\SUMMIT\BANK 1\CB 1102
1 B		14.95297	2	002-1 B\AOR 3\EAST BAY\BERKELEY F\12kv Bus 1 (FG-1, FG-2, FG-3, FG-4)\CB 1102	\AOR 3\EAST BAY\BERKELEY F\12kv Bus 1 (FG-1, FG-2, FG-3, FG-4)\CB 1102
1 B	1.993846	16.94682	2	002-1 B\AOR 3\EAST BAY\OAKLAND C\12kv Bus 1 (Banks 1, 3)\CB 1104	\AOR 3\EAST BAY\OAKLAND C\12kv Bus 1 (Banks 1, 3)\CB 1104
1 B		16.94682	2	002-1 B\AOR 3\EAST BAY\OAKLAND X\12kv BUS 1\CB 1107/12	\AOR 3\EAST BAY\OAKLAND X\12kv BUS 1\CB 1107/12
1 B		16.94682	2	002-1 B\AOR 3\EAST BAY\OAKLAND X\12kv BUS 2\CB 1107/22	\AOR 3\EAST BAY\OAKLAND X\12kv BUS 2\CB 1107/22
1 B	3.36	20.30682	2	002-1 B\AOR 3\EAST BAY\OAKLAND X\BANK 5\CB 1107	\AOR 3\EAST BAY\OAKLAND X\BANK 5\CB 1107
1 B	3.122637	23.42945	2	002-1 B\AOR 5\DE ANZA\WHISMAN\BANK 3\CB 1111	\AOR 5\DE ANZA\WHISMAN\BANK 3\CB 1111
1 B	3.386145	26.8156	2	002-1 B\AOR 5\SAN JOSE\HICKS\BANK 2\CB 1109	\AOR 5\SAN JOSE\HICKS\BANK 2\CB 1109
1 B	1.45	28.2656	2	002-1 B\AOR 8\FRESNO\CARUTHERS\BANK 2\CB 1104	\AOR 8\FRESNO\CARUTHERS\BANK 2\CB 1104
1 C		28.2656	3	003-1 C\AOR 1\HUMBOLDT\ORICK\BANK 1\CB 1101	\AOR 1\HUMBOLDT\ORICK\BANK 1\CB 1101
1 C		28.2656	3	003-1 C\AOR 3\DIABLO\CONTRA COSTA\Bank 9\CB 2111	\AOR 3\DIABLO\CONTRA COSTA\Bank 9\CB 2111
1 C		28.2656	3	003-1 C\AOR 3\EAST BAY\OAKLAND X\UNIT SUB#2\CB 402	\AOR 3\EAST BAY\OAKLAND X\UNIT SUB#2\CB 402
1 C	2.341807	30.6074	3	003-1 C\AOR 4\SAN FRANCISCO\POTRERO (SF A)\BANK 2\CB 1119	\AOR 4\SAN FRANCISCO\POTRERO (SF A)\BANK 2\CB 1119

[FreemanZ]

hi @Anonymous 

from your sample code, it seems your model involves multiple tables. It is hard to say where to start. please try to decompose the challenge, isolation and tackle them one by one.

[Anonymous]

I have two tables one is main table('EEP vwMWValue') columns ID,sublock,elementpath, value(integer). sublock and elementpath forms unique key. and other one is for slicer ('EEP BlockSubBlock').

if user selects a slicer value '4A', report visual needs to be started from subblock 4A. if user select slicer value 14R report visual needs to be shown from 14R.slicer table acts as lookup i have given value for 1A as 1.1B as 2, 14R as 249, 14s as 250, to store in slicer table.

values need to be shown like round necklace( 1A to 14S).

to provide that visual order ,dynamic offset/distance based on the selected slicer value is calculated. Here's a breakdown of the calculation:

D_S_M =

VAR MaxValue = MAXX(ALL('EEP BlockSubBlock'),'EEP BlockSubBlock'[ID])

VAR SlicerValue = SELECTEDVALUE('EEP BlockSubBlock'[ID])

RETURN

    IF(min('EEP vwMWValue'[ID]) >= SlicerValue,

       RIGHT("00"& (min('EEP vwMWValue'[ID]) - SlicerValue),3),

       RIGHT("00"& (min('EEP vwMWValue'[ID])  +(MaxValue- SlicerValue)),3)

    )

;;

another measure calculation: 

Uniquekey_M = [D_S_M]&"-"&MIN('EEP vwMWValue'[SubBlockElementPath]);

RT_M =

var currentkey = [Uniquekey_M]

 RETURN CALCULATE(SUM('EEP vwMWValue'[Value]),FILTER(ALL('EEP vwMWValue'), 'EEP vwMWValue'[Uniquekey_M] <= currentkey))

 ------

report visual is ordered based on measure Uniquekey_M. i verfied above measures D_S_M and Uniquekey_M working as expected. The issue is with running total measure(RT_M).running total (RT_M) is working as expected 80 percent of time when i select different/change slicer value. but sometimes its not including the first block rows in calculation.In below screen shot we can see that when i select slicer value 14S it didnt include 14s rows, it started running total calculation from 1A.please help.let me know i will provide pbix