Forum Discussion
Best Practice to filtering Fact Table (by FactTable column value)
It's almost always better to filter on columns rather than tables whenever possible. So I'd recommend something more like this:
Test # of Units with Fees = CALCULATE ( COUNTROWS ( 'fFactTable' ), 'fFactTable'[Admin Fee] <> 0, KEEPFILTERS ( TREATAS ( { "New Business", "Renewal" }, 'dransType'[GroupedTranType] ) ) )
It's almost always better to filter on columns rather than tables whenever possible. So I'd recommend something more like this:
Test # of Units with Fees =
CALCULATE (
COUNTROWS ( 'fFactTable' ),
'fFactTable'[Admin Fee] <> 0,
KEEPFILTERS (
TREATAS ( { "New Business", "Renewal" }, 'dransType'[GroupedTranType] )
)
)
So are we saying that the fFactTable filter is only filtering that column of the fFactTable?
KEEPFILTERS, appears to be a protection for context transition
TREATAS, this appears to be a nice way of filtering - in affect this is creating a mini table in memory and filtering that (is that correct)?
I also understand if I need to propogate filters over tables (related or not), this is likely the best solution method
Yes, the KEEPFILTERS function is there to preserve the local filter context. If that were not there, then the filter overwrites the context instead of further filtering the existing context.
Yes, TREATAS allows you to define a "mini" table while preserving data lineage. It's one of the more efficient methods of filtering. I recommend reading this for more detail:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/Thank you for the additional information Alexis - it's appricated
Hi, 101Mathew
If your problem has been solved, you could accept AlexisOlson 's reply as solution to close this thread.
Other community members will easily find the solution when they get the same issue.
Best Regards,
Community Support Team _ Eason
