Beginner - conditional filtering?

[DennisSchlein]

Hi 

Bare with me, i'm all new to Power BI.

I have a dataset that needs to be filtered, based on a certain value.

Criteria	ScanDate	ScanTypeId	Requested pickup date
1	12-07-2021	null	01-07-2021
1	12-07-2021	14	01-07-2021
1	null	15	01-07-2021
1	null	16	01-07-2021
1	null	14	01-07-2021
1	null	null	01-07-2021
2	01-07-2021	3	01-07-2021
2	03-06-2021	null	null
2	01-07-2021	16	01-07-2021
2	03-06-2021	14	01-07-2021
2	01-07-2021	16	01-07-2021
2	03-06-2021	1	null

I wanna have a filter on Criteria.

If Criteria is 1 - Then Requested pickup date is not null
Else if

Criteria is 2 - Then scan date is not null and scan type id is 14,15,16.

But the basic of how to do this, is this a meassure? Do I split my dataset into two, or?

Thanks in advance
Kind regards

[v-janeyg-msft · Accepted solution]

Hi, @DennisSchlein 

If you want to filter data before you use it in desktop, you can create a custom step in pq.

Like this:

= Table.SelectRows(#"Changed Type1", each  [Criteria]=1 and [Requested pickup date]<>null or [Criteria]=2 and [ScanDate]<>null and List.Contains({14,15,16},[ScanTypeId]) 
/If you don’t want to limit other situations/
or [Criteria]>2)

If you want to use it in table visual, you can create measure then use it in filter pane to filter data.

Like this:

Measure =
IF (
    MAX ( 'Table (2)'[Criteria] ) = 1
        && MAX ( 'Table (2)'[Requested pickup date] ) <> BLANK (),
    1,
    IF (
        MAX ( 'Table (2)'[Criteria] ) = 2
            && MAX ( 'Table (2)'[ScanDate] ) <> BLANK ()
            && DISTINCT ( 'Table (2)'[ScanTypeId] ) IN { 14, 15, 16 },
        1,
        0
    )
)

Best Regards

Janey Guo

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

[DennisSchlein]

This is excactly what I was looking for, and it works like a charm! 

Thank you very much for the effort! 

[emilmorkeberg]

Hi,

You can add a calculated column to your table and use a combination of nested IFS and a SWITCH() to define the output of the column and use that to filter whatever you want. Go to "Modeling -> New Column" in your report and try using the following (you need to replace 'TableName')

ColumnFilter = 

// VARIABLES
var SelectedCriteria = SELECTEDVALUE('TableName'[Criteria])
var SelectedPickupDate = SELECTEDVALUE('TableName'[Requested pickup date])
var SelectedScanDate = SELECTEDVALUE('TableName'[ScanDate])
var SelectedScanTypeId = SELECTEDVALUE('TableName'[ScanTypeId])

Return

// FORMULA

IF(SelectedCriteria = 1,IF(SelectedPickupDate <> "null","Include","Exclude"),

IF(SelectedCriteria = 2,
SWITCH(TRUE(),
SelectedScanDate <> "null","Include",
SelectedScanTypeId = 14,"Include",
SelectedScanTypeId = 15,"Include",
SelectedScanTypeId = 15,"Include",
"Exclude"),"Exclude")

Afterwards, you can use this new calculated column to exclude instances that doesn't meet your defined requirements, by filtering page/visual with "Include".

If this doesn't solve your problem, please specify your issue.

More about the Switch function here: https://docs.microsoft.com/en-us/dax/switch-function-dax