Forum Discussion
Create filter based on filtered dataset result AND use filtered value as a constant in measure
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!
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
3 Replies
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
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.
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
