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 col...
  • 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.

     

     

     

     

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.