Struggling with the filter measure!

[CARUSO93]

Hi,

I am trying to create some measures that will filter different things. 

First I want to filter a column containing product group.
I want the measure to display or give me all product groups that is less than P998 ( in other words <P998).
The product groups are all in the SC01037 column.

My measure looks like this, and there's obviously something I am doing wrong.

What am I doing wrong?

Secondly.
Is it possible to filter on all dates before today (as today date will change as the days goes by obviously?

I have a column with the material reservation date and want to filter: <TODAY.

How do I do that?

Thirdly,

How do I filter the following: "<> DATE(1900,01,01)".
The column I want to filter is "Old date" and I want to display all dates before and after 1900-01-01.
How do I do that?

Lastly,
I have 2 columns Planning date (MP92028) and Delivery date (MP92002) and want to filter so the measure returns to me only dates the dates where the planning is greater/after the delivery date. In other words: MP921000[MP92028] > MP921000[MP92002].

How do I do that?

Many thanks!!

[CARUSO93]

@v-yiruan-msft 

Here some sample data from Table MP921000, from column MP92010.

MP92010
1900-01-01
2022-09-26
2021-06-30
2021-11-26
2022-02-17
2022-04-26
2022-05-20
2022-08-25
2022-10-10
2022-10-13
2022-11-02
2022-12-27
2023-12-16
2024-04-25
2024-06-20

Let me know if you want me to add or alter the information in any way.

With kind regards,

/C

[v-yiruan-msft · Accepted solution]

Hi @CARUSO93 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Method1:

1. Create a measure as below 

Flag = IF(SELECTEDVALUE(MP921000[MP92010])<>DATE(1900,1,1),1,0)

2. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

Method2: Create a calculated table  using the below formula

Table = FILTER( 'MP921000', 'MP921000'[MP92010]<>DATE(1900,1,1))

Best Regards

[CARUSO93]

@v-yiruan-msft ,
I need to be a super user to attach files here but I will upload some example data here. Give me a few minutes.

With kind regards,
/C 

[CARUSO93]

@v-yiruan-msft 
Many, many thanks for your reply.

I get this for this measure. What does it mean by 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'.

The table is MP921000 and the column is MP92010. It's from the same table....Hmmm....

With kind regards,
C

[v-yiruan-msft]

Hi @CARUSO93 ,

What you are trying to create is a measure, right? If yes, the Filter function will return a table not a scalar value. Hence it will get that error message if you're creating a measure. You can create a calculated table as below screenshot using the formula: Table  = FILTER( 'MP921000', 'MP921000'[MP92010]<>DATE(1900,1,1))

If the above one is not your expected result, please provide more raw data in your table MP921000 (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft]

Hi @CARUSO93 ,

The following ones are the answers for your questions:

1. Display or give me all product groups that is less than P998 ( in other words <P998)

A: For the Text type field [SC01037], we can't compare directly using the greater than or less than signs. Before we can do that, we need to do some conversions and you can refer to the following formula to update your metric values.

Upd LP01 Brister i plockveckan =
VAR _selsc37 =
    SELECTEDVALUE ( SC011000[SC01037] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'SC011000'[SC01037] ),
        FILTER (
            SC011000,
            VALUE ( RIGHT ( SC011000[SC01037], LEN ( 'SC011000'[SC01037] ) - 1 ) ) < 998
        )
    )
RETURN
    IF ( _selsc37 IN _tab, 1, 0 )

2. Have a column with the material reservation date and want to filter: <TODAY

 FILTER('Tablename','Tablename'[material reservation date] < TODAY ())

3.  Filter is "Old date" and I want to display all dates before and after 1900-01-01

FILTER( 'Tablename','Tablename'[Old date]<> DATE(1900,01,01))

4. Return only dates the dates where the planning is greater/after the delivery date

FILTER( 'MP921000',MP921000[MP92028] > MP921000[MP92002])

If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[daXtreme]

Filtering a full table under CALCULATE(TABLE) will get you into troubles sooner than you might think.

Not:

CALCULATETABLE (
        VALUES ( 'SC011000'[SC01037] ),
        FILTER (
            SC011000,
            VALUE ( RIGHT ( SC011000[SC01037], LEN ( 'SC011000'[SC01037] ) - 1 ) ) < 998
        )
    )

but rather:

CALCULATETABLE (
    VALUES ( 'SC011000'[SC01037] ),
    KEEPFILTERS(
        VALUE ( RIGHT ( SC011000[SC01037], LEN ( 'SC011000'[SC01037] ) - 1 ) ) < 998
    )
)

By the way... Performing string extraction like the above is totally suboptimal. It'll tremendously slow down DAX. What should have been done instead is the extraction of the interesting portion in Power Query into its own column, then hiding the column from users' view and performing the filter on the column itself.

[daXtreme]

Before using any DAX function you should get familiar with how it works and what the correct syntax is. Here's the description with some good interactive examples of FILTER: http://dax.guide/filter. I'd also look at this: Filtering Tables in DAX - SQLBI.

Obviously, you're not using the function in the above code correctly, so please take some time to read the articles I've pointed out. Thanks.