Forum Discussion

OffermansWE's avatar
OffermansWE
Frequent Visitor
1 year ago
Solved

calculatetable with conditional filter

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. 
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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.

     

     

     

     

3 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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's avatar
    vivek31
    Resolver II
    1 year ago

    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.

  • OffermansWE's avatar
    OffermansWE
    Frequent Visitor
    1 year ago

    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.