cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CARUSO93
Helper III
Helper III

Struggling with the filter measure!

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.

CARUSO93_0-1664529745798.png

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!!

1 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)

yingyinr_0-1665021872399.png

Method2: Create a calculated table  using the below formula

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

yingyinr_1-1665022040193.png

Best Regards

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

View solution in original post

8 REPLIES 8
CARUSO93
Helper III
Helper III

@yingyinr 

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

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)

yingyinr_0-1665021872399.png

Method2: Create a calculated table  using the below formula

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

yingyinr_1-1665022040193.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CARUSO93
Helper III
Helper III

@yingyinr ,
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
Helper III
Helper III

@yingyinr 
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'.

 

CARUSO93_1-1664787274506.png

 

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

 

With kind regards,
C

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))

yingyinr_0-1664789159138.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
yingyinr
Community Support
Community Support

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 )

yingyinr_0-1664777535869.png

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

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

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
Super User
Super User

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors