Forum Discussion
Multiple Filter need to be applied by condition
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 __DS0CoreHi 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!
8 Replies
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 __DS0CoreYou 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=04iw9SYI2HN80HKSThanks 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."}},
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
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!
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.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.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.
