Create a new table from existing one using slicer values

[Meri93]

Hello everyone,

I have a Table t with 3 columns - Name of the company, Financial item and Value of the financial item. Financial item represents the name of the certain item in financial report, like cost of employees, revenue from online sales etc.. I am trying to make a dashboard where user can select multiple finacials items and for each item select different range of values. For example, user wants to see all companies that have revenue from online sales bigger than 100k and at the same time tax liabilities lower than 10k.

This is what I have so far:

I created 4 additional tables (Item1,Value1,Item2,Value2). Item1 and Item 2 are identical and they have only one column - Financial_Item which contains distinct Financial Items. Value1 and Value2 are also identical and they contain all values of the Financial items from the original table (Column name = Value). Then, I made 4 slicers:
1. Two dropdown slicers from which you can choose Financial item. One slicer refers to Item1 and other to Item2.
2. Two Range slicer where you select range of values for that Financial item. One slicer refers to Value1 and other to Value2.

Then, I created following measures:
1. Select_Item1 = SELECTEDVALUE(Item1[Financial_Item])
2. Select_Min1 = CALCULATE(MIN(Value1[Value]); allselected(Value1))
3. Select_Max1 = CALCULATE(MAX(Value1[Value]); allselected(Value1))
4. Select_Item2 = SELECTEDVALUE(Item2[Financial_Item])
5. Select_Min2 = CALCULATE(MIN(Value2[Value]); allselected(Value2))
6. Select_Max2 = CALCULATE(MAX(Value2[Value]); allselected(Value2))

So far, every step works perfectly, but I encounter a problem in the next step in which I try to make a new table that contains list of the companies with given conditions using calculated measures. I tried using FILTER, CALCULATETABLE, but nothing has worked so far. I think that the problem is that the measures I have created are not global variables so I can not use them in creating new tables.

Does someone know how can I create global variables from the selected slicer values so I can create new table or maybe can suggest some other solution?

Thank you!

[parry2k]

@Meri93 it will be easier to understand the problem if you provide sample data in table format and the expected output.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Meri93]

Hi @parry2k ,

Here is sample table:

Name of the company	Financial Item	Value
A	Revenue from online sales	110.000,00
A	Other revenue	10.000,00
A	Cost of revenue	30.000,00
A	Cost of employees	20.000,00
A	Income before tax	70.000,00
A	Interest income	5.000,00
A	Tax	8.400,00
A	Net income	61.600,00
B	Revenue from online sales	105.000,00
B	Other revenue	40.000,00
B	Cost of revenue	20.000,00
B	Cost of employees	10.000,00
B	Income before tax	115.000,00
B	Interest income	1.000,00
B	Tax	13.800,00
B	Net income	101.200,00
C	Revenue from online sales	150.000,00
C	Other revenue	5.000,00
C	Cost of revenue	100.000,00
C	Cost of employees	50.000,00
C	Income before tax	5.000,00
C	Interest income	1.000,00
C	Tax	600,00
C	Net income	4.400,00
D	Revenue from online sales	10.000,00
D	Other revenue	1.000,00
D	Cost of revenue	6.000,00
D	Cost of employees	6.000,00
D	Income before tax	-1.000,00
D	Interest income	1.000,00
D	Tax	0,00
D	Net income	-1.000,00

The user will choose financial Item and value range for that financial item. The output shold be the list of companies that meet those conditions. For example, user wants to see which companies have Revenue from online sales bigger than 100.000,00 and at the same time Tax lower than 10.000,00.  In our example, only A and C companies meet those conditions so the output should look like this:

Name of the company

A

C

[ante87]

Has someone maybe figured out how to do this? I am also interested in solution.

[Meri93]

@ante87 , unfortunately still nothing... I will post it here if I figure out the solution.