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