Best Practice to filtering Fact Table (by FactTable column value)

[101Mathew]

Hi All,

Please could I ask for some guideance.

I have a fact table with say 2m rows of data - I need to return the count of rows when the fees are not equal to zero and the transaction type is New Business or Renewal.

I believe I have a solution, but it does not feel like it's best practice.

Test # of Units with Fees =
CALCULATE (
    COUNTROWS ( 'fFactTable' ),
    'fFactTable'[Admin Fee] <> 0,
    FILTER (
        'dTransType',
        'dransType'[GroupedTranType] = "New Business"
            || 'dTransType'[GroupedTranType] = "Renewal"
    )
)

I need a lot of the fFactTable columns, to enable dynamic filtering via the DimTable, for user report interaction

This solution works, but does not seem to be to be best practice, as it filters the whole of the fact table row by row - I cannot currently (with my limited DAX knowledge) see a better solution.  

Thank you in advance,

Regards,

Mathew

[AlexisOlson · Accepted solution]

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] )
    )
)

[101Mathew]

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

[AlexisOlson]

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/

[101Mathew]

Thank you for the additional information Alexis - it's appricated

[v-easonf-msft]

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

[amitchandak]

@101Mathew , Try like

Test # of Units with Fees =
CALCULATE (
COUNTROWS ( 'fFactTable' ),
Filter( 'fFactTable', 'fFactTable'[Admin Fee] <> 0 ),
FILTER (
'dTransType',
'dransType'[GroupedTranType] in {"New Business", "Renewal"}
)
)

[101Mathew]

I like your change the filter of dTransType with the in function.

Question for you 1 why wrap fFactTable with a filter function?
Question 2 for you - is there not a better approach, then doing a brute force filter interating over the whole of the fact table?