Multiple Filter need to be applied by condition

[jiangxm80]

I have multiple filter, when I do the calculation, how could I apply those filters according to other conditions? 

VAR __DS0FilterTable = 

TREATAS({@{variables('strVersionNo')}}, 'SalesForecastDetails'[VersionNo])

VAR __DS1FilterTable = 

TREATAS({@{variables('strProductGroup')}}, 'SalesForecastDetails'[ProductGroup])

VAR __DS2FilterTable = 

TREATAS({@{variables('strCategory')}}, 'SalesForecastDetails'[Category])

VAR __DS0Core = 

CALCULATETABLE(

SUMMARIZE(

'SalesForecastDetails',

'SalesForecastDetails'[YearNo],

'SalesForecastDetails'[MonthNo],

'SalesForecastDetails'[VersionNo],

'SalesForecastDetails'[Region],

'SalesForecastDetails'[Area],

'SalesForecastDetails'[ProductGroup],

'SalesForecastDetails'[Category],

'SalesForecastDetails'[SubCategory],

'SalesForecastDetails'[ProductNo],

'SalesForecastDetails'[ProductSpec],

'SalesForecastDetails'[FinalQty_Accum],

'SalesForecastDetails'[FinalAmt_Accum],

'SalesForecastDetails'[Left],

'SalesForecastDetails'[LeftAmt],

'SalesForecastDetails'[Sample],

'SalesForecastDetails'[SampleAmt],

'SalesForecastDetails'[Inv],

'SalesForecastDetails'[InvAmt],

'SalesForecastDetails'[FinalQty],

'SalesForecastDetails'[FinalAmt],

'SalesForecastDetails'[LInv],

'SalesForecastDetails'[LInvAmt]

),

KEEPFILTERS(__DS0FilterTable),

'SalesForecastDetails'[FinalQty_Accum]<>0 || 'SalesForecastDetails'[FinalAmt_Accum]<>0 || 'SalesForecastDetails'[Left]<>0 || 'SalesForecastDetails'[LeftAmt]<>0 || 'SalesForecastDetails'[Sample]<>0 || 'SalesForecastDetails'[SampleAmt]<>0 || 'SalesForecastDetails'[Inv]<>0 || 'SalesForecastDetails'[InvAmt]<>0 || 'SalesForecastDetails'[FinalQty]<>0 || 'SalesForecastDetails'[FinalAmt]<>0 || 'SalesForecastDetails'[LInv]<>0 || 'SalesForecastDetails'[LInvAmt]<>0

)

EVALUATE

__DS0Core

[v-priyankata]

Hi @jiangxm80 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @jiangxm80 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. If yes, marking the solution would be awesome for others who might run into the same thing.

[v-priyankata]

Hi @jiangxm80 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @jiangxm80  , 

Thank you for reaching out to the Microsoft Fabric Forum Community.

1. your issue happens because you're applying a filter like keepfilters(__DS2FilterTable) even when __DS2FilterTable is blank.

2. In dax, that's not allowed — keepfilters(blank) doesn’t create a valid filter context, so it throws this error:
"the true/false expression does not specify a column."

3. To fix it, apply each filter only if it has a value. wrap each calculatetable step with if, instead of passing all filters at once.

If solution was helpful, please mark it as Accept Answer.

Feel free to let us know if you have any further questions—we’re here to help!

[Poojara_D12]

Hi @jiangxm80 

Your DAX script uses TREATAS to dynamically apply filters on the SalesForecastDetails table. To apply these filters conditionally based on other conditions, you can introduce logic in your DAX query by combining the IF, SWITCH, or conditional logic functions.

Below is an example of how you can adapt your script to include conditional logic for applying the filters:

VAR __DS0FilterTable = 
    IF(
        NOT(ISBLANK(@{variables('strVersionNo')})), 
        TREATAS({@{variables('strVersionNo')}}, 'SalesForecastDetails'[VersionNo]),
        BLANK()
    )

VAR __DS1FilterTable = 
    IF(
        NOT(ISBLANK(@{variables('strProductGroup')})), 
        TREATAS({@{variables('strProductGroup')}}, 'SalesForecastDetails'[ProductGroup]),
        BLANK()
    )

VAR __DS2FilterTable = 
    IF(
        NOT(ISBLANK(@{variables('strCategory')})), 
        TREATAS({@{variables('strCategory')}}, 'SalesForecastDetails'[Category]),
        BLANK()
    )

VAR __DS0Core = 
    CALCULATETABLE(
        SUMMARIZE(
            'SalesForecastDetails',
            'SalesForecastDetails'[YearNo],
            'SalesForecastDetails'[MonthNo],
            'SalesForecastDetails'[VersionNo],
            'SalesForecastDetails'[Region],
            'SalesForecastDetails'[Area],
            'SalesForecastDetails'[ProductGroup],
            'SalesForecastDetails'[Category],
            'SalesForecastDetails'[SubCategory],
            'SalesForecastDetails'[ProductNo],
            'SalesForecastDetails'[ProductSpec],
            'SalesForecastDetails'[FinalQty_Accum],
            'SalesForecastDetails'[FinalAmt_Accum],
            'SalesForecastDetails'[Left],
            'SalesForecastDetails'[LeftAmt],
            'SalesForecastDetails'[Sample],
            'SalesForecastDetails'[SampleAmt],
            'SalesForecastDetails'[Inv],
            'SalesForecastDetails'[InvAmt],
            'SalesForecastDetails'[FinalQty],
            'SalesForecastDetails'[FinalAmt],
            'SalesForecastDetails'[LInv],
            'SalesForecastDetails'[LInvAmt]
        ),
        KEEPFILTERS(__DS0FilterTable),
        KEEPFILTERS(__DS1FilterTable),
        KEEPFILTERS(__DS2FilterTable),
        'SalesForecastDetails'[FinalQty_Accum] <> 0 
        || 'SalesForecastDetails'[FinalAmt_Accum] <> 0 
        || 'SalesForecastDetails'[Left] <> 0 
        || 'SalesForecastDetails'[LeftAmt] <> 0 
        || 'SalesForecastDetails'[Sample] <> 0 
        || 'SalesForecastDetails'[SampleAmt] <> 0 
        || 'SalesForecastDetails'[Inv] <> 0 
        || 'SalesForecastDetails'[InvAmt] <> 0 
        || 'SalesForecastDetails'[FinalQty] <> 0 
        || 'SalesForecastDetails'[FinalAmt] <> 0 
        || 'SalesForecastDetails'[LInv] <> 0 
        || 'SalesForecastDetails'[LInvAmt] <> 0
    )


EVALUATE
__DS0Core

You can extend the logic further by introducing additional conditions (e.g., based on other column values or external variables) in the IF statements.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[jiangxm80]

Thanks for the help. 

it seems some error as the folloiwng.

{"type":1,"value":"Query (8, 13) Too few arguments were passed to the ISBLANK function. The minimum argument count for the function is 1."}},

[jennratten]

Hi @jiangxm80  - 

The error thrown is stating that the ISBLANK function is being called without a required argument.  The argument could be a value or an expression to check for blank.  In your script, it looks like one of the variables @{variables('strVersionNo')}, @{variables('strProductGroup')}, or @{variables('strCategory')} might not be successfully resolved, which could be why the ISBLANK function is executing without an argument.

Here are some suggestions:

• Make sure the are correctly defined and values are being passed to the query, making sure that these are not null or empty.  You can do this by adding a step to view the values of these variables before using them in the ISBLANK function. 
• Modify the DAX script to confirm whether or not the variables are the cause of the problem or if it is something else, but replacing the variable values with some placeholder values. Below is an example of how you can do this. Replace "Version1", "ProductGroup1", and "Category1" with the actual values or ensure that the variables are correctly passed.

VAR __DS0FilterTable =
IF(
NOT(ISBLANK("Version1")),
TREATAS({"Version1"}, 'SalesForecastDetails'[VersionNo]),
BLANK()
)

VAR __DS1FilterTable =
IF(
NOT(ISBLANK("ProductGroup1")),
TREATAS({"ProductGroup1"}, 'SalesForecastDetails'[ProductGroup]),
BLANK()
)

VAR __DS2FilterTable =
IF(
NOT(ISBLANK("Category1")),
TREATAS({"Category1"}, 'SalesForecastDetails'[Category]),
BLANK()
)

VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'SalesForecastDetails',
'SalesForecastDetails'[YearNo],
'SalesForecastDetails'[MonthNo],
'SalesForecastDetails'[VersionNo],
'SalesForecastDetails'[Region],
'SalesForecastDetails'[Area],
'SalesForecastDetails'[ProductGroup],
'SalesForecastDetails'[Category],
'SalesForecastDetails'[SubCategory],
'SalesForecastDetails'[ProductNo],
'SalesForecastDetails'[ProductSpec],
'SalesForecastDetails'[FinalQty_Accum],
'SalesForecastDetails'[FinalAmt_Accum],
'SalesForecastDetails'[Left],
'SalesForecastDetails'[LeftAmt],
'SalesForecastDetails'[Sample],
'SalesForecastDetails'[SampleAmt],
'SalesForecastDetails'[Inv],
'SalesForecastDetails'[InvAmt],
'SalesForecastDetails'[FinalQty],
'SalesForecastDetails'[FinalAmt],
'SalesForecastDetails'[LInv],
'SalesForecastDetails'[LInvAmt]
),
KEEPFILTERS(__DS0FilterTable),
KEEPFILTERS(__DS1FilterTable),
KEEPFILTERS(__DS2FilterTable),
'SalesForecastDetails'[FinalQty_Accum] <> 0
|| 'SalesForecastDetails'[FinalAmt_Accum] <> 0
|| 'SalesForecastDetails'[Left] <> 0
|| 'SalesForecastDetails'[LeftAmt] <> 0
|| 'SalesForecastDetails'[Sample] <> 0
|| 'SalesForecastDetails'[SampleAmt] <> 0
|| 'SalesForecastDetails'[Inv] <> 0
|| 'SalesForecastDetails'[InvAmt] <> 0
|| 'SalesForecastDetails'[FinalQty] <> 0
|| 'SalesForecastDetails'[FinalAmt] <> 0
|| 'SalesForecastDetails'[LInv] <> 0
|| 'SalesForecastDetails'[LInvAmt] <> 0
)

EVALUATE
__DS0Core

[jiangxm80]

thanks, I have tried, should be fine now. 

but there is a new issue, when the __DS2FilterTable is BLANK(), it will show some error as the following.

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column