Forum Discussion

DennisSchlein's avatar
DennisSchlein
Helper III
5 years ago
Solved

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 ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

emilmorkeberg's avatar
emilmorkeberg
Frequent Visitor
5 years ago

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