Forum Discussion
Need help with this filter and table link
Hello all,
I have a bit problem well for me that still new in this power BI (I used to use with Tableau) , since I had to migrate to Power BI, I have learning this Power BI that it is completly different than Tableau. Anyway, I need help. please hep.
I have 3 tables:
Table 1:
Index Category Type Value
| 1 | ENROLLED | A | 824 |
| 1 | ENROLLED | B | 0 |
| 1 | ENROLLED | C | 0 |
| 1 | ENROLLED | D | 0 |
| 1 | ENROLLED | E | 0 |
| 2 | OFFERED | A | 518 |
| 2 | OFFERED | B | 114 |
| 2 | OFFERED | C | 104 |
| 2 | OFFERED | D | 175 |
| 2 | OFFERED | E | 800 |
| 3 | ACCEPTED | A | 320 |
| 3 | ACCEPTED | B | 57 |
| 3 | ACCEPTED | C | 35 |
| 3 | ACCEPTED | D | 56 |
| 3 | ACCEPTED | E | 334 |
| 4 | PARTNER | A | 359 |
| 4 | PARTNER | B | 85 |
| 4 | PARTNER | C | 37 |
| 4 | PARTNER | D | 57 |
| 4 | PARTNER | E | 301 |
Table 2: (I create this table using Power BI for Customize/Manual Sort)
| Category | Sort |
| ENROLLED | 1 |
| OFFERED | 2 |
| ACCEPTED | 3 |
| PARTNER | 4 |
Table 3: (I created this table using Power BI, with formula for %_Column = IF('Table3'[Index]=1; BLANK();
'Table3'[Summary]/MAXX(FILTER('Table3';'Table3'[Index]=EARLIER('Table3'[Index])-1);'Table3'[Summary)*100)
This formula is to achive value in %_Column - (1711/824)*100 207.., (802/1711)*100
| Index | CATEGORY | SUMMARY | %_Column |
| 1 | ENROLLED | 824 | |
| 2 | OFFERED | 1711 | 207 |
| 3 | ACCEPTED | 802 | 46 |
| 4 | PARTNER | 839 | 104 |
Problems:
When I did filter base on Type in Table 1: A or B or C or D or E. It doesn work or did not show resul in the chart. (I am using bar chart). I know there is somehting wrong with the link or filter.
How to achive so the filter would work? pleaseeee help.
Any help will much appreciated it!
Thank you in advance.
EH
14 Replies
- amitchandak
Super User
Anonymous , As a new column this should have worked
try like
%_Column =
'Table3'[Summary]/MAXX(FILTER('Table3';'Table3'[Index]=EARLIER('Table3'[Index])-1);'Table3'[Summary])*100Will work if Table 3 is created like this
Table 3 = summarize(Table1, Table1[index],Table[Category],"Value",sum(Table[Value]))
- AllisonKennedy
Community Champion
amitchandak Your result works, but I think the issue is that it won't filter by Type. Anonymous Please let me know if this assessment of problem is correct? :
Table 1 has 5 Types (A, B, C, D, E) for each Index, and if I'm understanding your question correctly, you want to filter the % calculation by Type. Since Table 3 has no reference/link to Type, and each type is present for each index, it won't give any different results when slicer selection changes for Type.
Anonymous Maybe you can try to explain your problem again and show example of your desired results?
yes, AllisonKennedy exactly what you said... that what I encounter the problem. What should I do? thank you
- AllisonKennedy
Anonymous
Lots going on here. First, you need to understand the relationships between these tables, which you have not described.
Table3 is a SUMMARY table, so you cannot filter it by type, because it is at a higher level and has no reference to Type.
What are you exactly trying to achieve? Do you just have the 4 indexes? If so, you could just create a MEASURE for their totals and then create measure or measures to calculate the percentages.
v-xicaiCommunity Support
Hi Anonymous ,
You couldn't have a calculated table or column interacted with a slicer or interaction , since calculated table or columns are only calculated when data is loaded/refreshed.
To achieve your demand, you may needn't to create another calculated table3, just create measure like DAX below in Table1, then display Table1[Category], Table1[Type], Table1[Value] and Table1[%_Result] in table visual.
%_Result= Var _LastValue= CALCULATE(SUM('Table1'[Value]),FILTER(ALLSELECTED('Table1'),'Table1'[Category]=MAX('Table1'[Category]) &&'Table1'[Type]=MAX('Table1'[Type])&&'Table1'[Index]=MAX('Table1'[Index])-1)) Var _Percent= DIVIDE( MAX('Table1'[Value]), _LastValue )*100 Return IF(_LastValue<> BLANK(), _Percent , 0)Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-xicai
Hi Anonymous ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
