topic Re: calculatetable with conditional filter in DAX Commands and Tips

calculatetable with conditional filter

OffermansWE — Tue, 04 Mar 2025 09:53:25 GMT

Hello,

I want to count the rows of a table filtered by two conditions. For this condition A always needs to be true. The second filter is conditional: if _onderscheid is not blank, the table column[onderscheid] should be equal to onderscheid, if the filter is blank, the additional filter is not required. The _onderscheid variable is a text value, or can be blank. The _onderscheid can be found in three columns in the table to be filtered.

I have created the VAR _table where I calculate a table like:

VAR _table = calculatetable(

'Unpivoted waarden',

'Unpivoted waarden'[Column_name] = _column_name,

('Unpivoted waarden'[BB/NB] in {(_onderscheid)} || 'Unpivoted waarden'[W/U] in {(_onderscheid)} || 'Unpivoted waarden'[Gebruik_woon] in {(_onderscheid)})

)

In this case, if I return countrows(_table) does return nothing for the values where _onderscheid is blank. How can I make sure that the second filter of the calculatetable function is not used when the _onderscheid value is blank?

I eventually need to perform a switch function on the countrows for an additional filter. Therefore, I cannot put the countrows(calculatetable()) in an if() function. So it would be best if the problem could be solved within assigning the _table variable.

Re: calculatetable with conditional filter

vivek31 — Tue, 04 Mar 2025 10:25:18 GMT

HI OffermansWE ,

you can use this formula to create table

VAR _table = CALCULATETABLE('Unpivoted waarden', 'Unpivoted waarden'[Column_name] = _column_name IF(NOT(ISBLANK(_onderscheid)), 'Unpivoted waarden'[BB/NB] = _onderscheid || 'Unpivoted waarden'[W/U] = _onderscheid || 'Unpivoted waarden'[Gebruik_woon] = _onderscheid))

then count the row
VAR _count = COUNTROWS(_table)
RETURN
_count

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Re: calculatetable with conditional filter

FarhanJeelani — Tue, 04 Mar 2025 10:53:07 GMT

Hi OffermansWE ,

You need to modify your CALCULATETABLE function so that the second filter condition is applied only when _onderscheid is not blank. When _onderscheid is blank, it should not filter the table based on BB/NB, W/U, or Gebruik_woon.

You can achieve this by modifying your VAR _table as follows:

VAR _table = CALCULATETABLE( 'Unpivoted waarden', 'Unpivoted waarden'[Column_name] = _column_name, IF( ISBLANK(_onderscheid), TRUE(), 'Unpivoted waarden'[BB/NB] = _onderscheid || 'Unpivoted waarden'[W/U] = _onderscheid || 'Unpivoted waarden'[Gebruik_woon] = _onderscheid ) )

This way, _table correctly includes all rows when _onderscheid is blank, and only filters based on _onderscheid when it is provided.

Now, when you use COUNTROWS(_table), it should return the correct count for both blank and non-blank cases of _onderscheid.

Please mark this post as solution if it helps you. Appreciate Kudos.

Re: calculatetable with conditional filter

OffermansWE — Tue, 04 Mar 2025 12:03:49 GMT

vivek31 As a result, i get the values for when _onmogelijk is not blank. However, I do not get any results when _onmogelijk is blank. The cells in the calculated column are blank for the cells where _onmogelijk is blank.