Forum Discussion
Beginner - conditional filtering?
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
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.
3 Replies
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
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.
This is excactly what I was looking for, and it works like a charm!
Thank you very much for the effort!
