Create filter based on filtered dataset result AND use filtered value as a constant in measure

[Astorre72]

Hi guys!

With this link you can download my dummy dataset:

https://www.dropbox.com/s/rh27eu9rvhbs1hg/DUMMY.xlsx?dl=0

I have a huge dataset, and this Excel file represents the structure of it. So I have an ID column, and two other values column ("RAW_DATA" sheet). This dataset is in an SQL table, and my BI reports are connected to it via DirectQuery.

The consumer of the report chooses the ID in a slicer. This way e.g. a line chart appears that contains only the values which have the selected ID. Easy so far. But I want to be able to give my users the opportunity to choose from the selected ID-s X value also! I want to create a slicer for these X values, and of course this slicer should contain only those X values which are determined by the choosen ID value. Sounds like an easy task, and maybe I am missing something... but I cannot solve this problem. BI wants load every X value in that slicer (which is impossible, because I have millions of X values...).

Once we are able to achieve this, we can move on to our next problem 🙂 

When the user selected the X value, I have to use that selected X value as a constant in other calculations... In my attached Excel file you can see a simplified version of the calculation on the "SELECTED_DATA" sheet. So basically the question is how to use the selected value of a slicer as variable in other formulas. These formulas I have to do in a measure (because I am using DirectQuery - therefore I cannot use computed columns)... But the question is: How? 

And the cherry on top: as you can see in the attached Excel my calculations are rolling... so they are always dependent on the values which are smaller than the current value...  I you have any ideas for this, please dont hold back 🙂

Thanks!

[Anonymous]

Hi @Astorre72 ,

You can create a measure as below by using SELECTEDVALUE function:

Measure =
CALCULATE (
    SUM ( 'Table'[X] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[ID] = SELECTEDVALUE ( 'Table'[ID] )
            && 'Table'[X] <= SELECTEDVALUE ( 'Table'[X] )
    )
)

If the above method is not working, please provide your desired result in form of screen shot or table, also provide the related calculation logic.

Best Regards

Rena

[Astorre72]

Hi, @Anonymous ,

Thanks for your reply! Your answer actually clears some of the confusion I had about the problem, but one thing still remains:

How can I use the selected value in a measure? You showed in your example how I can get the sum of values where X is smaller than the selected value. But how could I achieve something like in my DUMMY.xlsx file?

In the DUMMY.xlsx file on the "SELECTED_DATA" sheet I created a "Z" column. On the sheet in E2 represents the selected ID, and E3 represents the selected X value. And in E8:E57 I have a formula that uses the selected X value. The formula in the Excel is probably more complicated than it needs to be... (although if you know how to do it, I welcome it 🙂 ). But it would be enough for me to get simply for every X value the running total of [(SELECTEDVALUE X - current X)*current Y]. Something like this... Because if I could understand this (how to use the selected value in a formula), than I think I would be able to figure out the rest.

[Anonymous · Accepted solution]

Hi @Astorre72 ，

You can follow the below steps to achieve it:

1. Create a calculated table Table 2

Table 2 = SUMMARIZE('Table','Table'[ID],'Table'[X])

2. Use the field X of Table 2 as slicer

3. Create a measure to get Z

Z? = (SELECTEDVALUE('Table 2'[X])- MAX('Table'[X]))*MAX('Table'[Y])

I created a sample pbix file, you can get it from this link.

Best Regards

Rena