Forum Discussion
Helper IIFilter Table by a measure does not work
Hello guys,
i have a little problem regarding the filter context between two tables by measure.
In Table (1) I have two columns. One with calculated margins and one with achieved margins. Are both available the amount of columns should be seperated in isolated Table (2) by measure 'No.':
No. = if(HASONEFILTER(Ergebnis[Ergebnis]);if(FIRSTNONBLANK(Ergebnis[Ergebnis];1)="SR: DB<kalk";CALCULATE(COUNT(Offer[L]);filter(Kalkulator;[DB ist]&&[DB kalk]<>blank());FILTER(Kalkulator;IFERROR([DB ist]-[DB kalk];0)<-0,05));if(FIRSTNONBLANK(Ergebnis[Ergebnis];1)="SR: DB>kalk";CALCULATE(COUNT(Offer[L]);filter(Kalkulator;[DB ist]&&[DB kalk]<>blank());FILTER(Kalkulator;IFERROR([DB ist]-[DB kalk];0)>0,05));if(FIRSTNONBLANK(Ergebnis[Ergebnis];1)="SR: DB~kalk";CALCULATE(COUNT(Offer[L]);filter(Kalkulator;[DB ist]&&[DB kalk]<>blank());FILTER(Kalkulator;IFERROR([DB ist]-[DB kalk];1)<0,05&&IFERROR([DB ist]-[DB kalk];-1)>-0,05));blank())));CALCULATE(COUNT(Offer[L]);filter(Kalkulator;[DB ist]&&[DB kalk]<>blank())))
But I can only filter from Table (1) to Table (2). So for example if I want to see in Table (1) only the two colums shown in the first Line of Table (2) i set the filter by clicking on this row (SR: DB<kalk). But nothing happens.
Do I have to creat a calculated column to connect it with Table (2) two get filter criteria from (2) to (1)?
Kind regards
Chris
Hi Chris_23
It is possible to build a measure by unrelated table. If and isfiltered function is a good way to try. And you need to filter your table by values in unrelated table in measure to get your result. All values need to filter one by one in filter function in your code. Or your measure will get a wrong result.
Can you share a sample like what you are dealing with to me by your Onedrive for Business? This may make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Chris_23 , You measure is not very clear. To filter a measure you can use the visual level filter.
or you need to force a context to filter a measure
example
sumx(filter(values(Table[ID]), [Measure] > 100), [Measure])
Hi Chris_23
Your measure seems to be complex, please share a sample by your Onedrive for Business.
This will make it easier for me to understand your requirement.
Did you build a one to many relationship?(Table1 one, Table2 many) You can try to change the cross filter direction from Single to Both. Or you can build a filter measure and add this measure into the visual filter field.
Filter measure = IF(your condition, 1,0)Then add this measure into visual filter and set the measure to show items when value =1.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Chris_23
Hi Anonymous
so do I have to build a relationship between the tables to get them into filter context. Is it not possible to filter from a Table with a measure which is not direct linked to the model?
Kind regards
Chris
Hi Chris_23
It is possible to build a measure by unrelated table. If and isfiltered function is a good way to try. And you need to filter your table by values in unrelated table in measure to get your result. All values need to filter one by one in filter function in your code. Or your measure will get a wrong result.
Can you share a sample like what you are dealing with to me by your Onedrive for Business? This may make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Measure =IF( ISBLANK(max(Other column Name)), BLANK() ,IF([Measure]))
