How do I show CERTAIN values in power bi

[BigTommy]

Hey Power Team!

I want show in table just certain values. Basically I have one table which shoing negative and positive values and want to split this between 2 tables which one will show only negative anbd secon only positive values. 

See below

item number	Item name	% variance	Eur variance
1	test 1	-0.3%	-100
2	test 2	+2%	200
3	test 3	+1.1%	150
1	test 1	-0.208%	-123

and I want to tables which will show:

Table 1 (NEGATIVE)

item number	Item name	% variance	Eur variance
1	test 1	-0.3%	-100
1	test 1	-0.208%	-123

Table 2 (POSITIVE)

item number	Item name	% variance	Eur variance
2	test 2	+2%	200
3	test 3	+1.1%	150

please help

Big T

[mahoneypat · Accepted solution]

On your report page in the Filters panel, when your table visual is selected, you should see the option to add a Filter to one of your measures.  Just set it to greater than 0 (for the positive table) and "Apply Filter".  Duplicate the table (Ctrl-C, Ctrl-V) and filter the new one for <0 (negative).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

On your report page in the Filters panel, when your table visual is selected, you should see the option to add a Filter to one of your measures.  Just set it to greater than 0 (for the positive table) and "Apply Filter".  Duplicate the table (Ctrl-C, Ctrl-V) and filter the new one for <0 (negative).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[BigTommy]

this is genius! 

simple!

Thank you!

[nandic · Accepted solution]

@BigTommy ,
If you need to create 2 visualizations (tables) - one showing positive values, second showing negative values, use @mahoneypat 's approach.
If you need to create separate tables (not visualization, but for modelling), you can used Dax for creating dynamic tables.
Formula: 

TableNegative = FILTER('Table',[variance]<0)
* [variance] is a measure

[BigTommy]

thank you very much! Both sollutions are great and can be used in diferent situations!

thanks!!

[nandic]

@BigTommy , I’m glad it worked out!

[nandic]

Hi @BigTommy ,
In Power Query you can make 2 new tables making reference to existing table. Right click on table and choose reference.
And in each of these 2 new tables add filter on variance column (first table filtering variance > 0, second table filtering variance < 0).
You can also make 2 new tables using Dax: 

PositiveValues = FILTER('Table','Table'[% variance]>0)
NegativeValues = FILTER('Table','Table'[% variance]<0)

Cheers,
Nemanja

[BigTommy]

Thank you for quick response!

Forgot to mention thet the % variance and euro variance is a measure. 

when I try use your method I have comment 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"