cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

Using table as filter in power bi

Hi all,

I am trying to solve this probably simple problem but came up with strange result. I have a tLevels table with different grouping levels like this:

 

Lev1     Lev2     ProductID

1        12       A

1        12       B

1        4321     C

2        133      D

2        12       E

 

then I have a sales tSales table:

 

ProductID  Amount

A          100

A           10

B          200

B          150

C           80

D          300

E          260

 

The tables are related via ProductID. I was expecting to sum ProductIDs by Lev1 with

 

SumAmount = CALCULATE(SUM(tSales[Amount]),tLevels[Lev1]) 

 

However I get this strange result 

 

 test.JPG

 

Any ideas why is it not summing by Lev1 giving me the same result on all lines?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
zivhimmel Member
Member

Re: Using table as filter in power bi

Well, it works well for me based on the sample data you provided.

You can check the desktop file I created with your data and compare to yours.

 

https://superfarb.com/wp-content/uploads/2017/07/Levels.pbix_.zip

 

 

 

Ziv Himmelfarb

https://www.superfarb.com

View solution in original post

7 REPLIES 7
tjd Established Member
Established Member

Re: Using table as filter in power bi

Anytime you see a result like this (all values returned are the same) it usually means that your table relationships are not set correctly between your tLevels and tSales tables.  Try looking at the relationship flow between your tables and select the other choice (one or both).

zivhimmel Member
Member

Re: Using table as filter in power bi

If you want to show Amount by Lev1,

the measure will simple be =SUM(Amount).

You don't even have to create a measure for it, just create a visual with tLevels[Lev1] and tSales[Amount]

and make sure the aggregation used for tSales[Amount] is SUM.

 

The problem with the measure you've created is that CALCULATE changes the context filter for the fields you are filtering by

(in this case tLevels[Lev1]). The second argument to CALCULATE is a flter. You just used tLevels[Lev1] as a filter,

which always returns TRUE, therefore all values are the same.

 

I hope that helps.

 

Ziv Himmelfarb

https://www.superfarb.com

 

gvg Member
Member

Re: Using table as filter in power bi

Thanks for the comments. I actually tried to put up a table visual without any measures before posting this question. It returns even more strange result:

 

test2.JPG

My table relationship looks like:

 

test3.JPG

 

It doesn't help if Cross filter direction is Both or Single.

zivhimmel Member
Member

Re: Using table as filter in power bi

Any page level or report level filters in use?

Highlighted
gvg Member
Member

Re: Using table as filter in power bi

No.

 

test4.JPG

zivhimmel Member
Member

Re: Using table as filter in power bi

Well, it works well for me based on the sample data you provided.

You can check the desktop file I created with your data and compare to yours.

 

https://superfarb.com/wp-content/uploads/2017/07/Levels.pbix_.zip

 

 

 

Ziv Himmelfarb

https://www.superfarb.com

View solution in original post

gvg Member
Member

Re: Using table as filter in power bi

Your example works fine. I investigated thoroughly my data set and found out that product A had some odd non-printables in tSales.

Thanks!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)