calculatetable with conditional filter

[OffermansWE]

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. 

[OffermansWE]

@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. 

[FarhanJeelani · Accepted solution]

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.

[vivek31]

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.