Filtering data

[Anonymous]

Hi all,

I hope someone can help me with the following.

below is a small dataset.

I want to set up a filter which gives me only the details of the orders in which ONLY partnr AMS300037 is mentioned.

In this example only order nr 8998429 and 8998478.

As the other orders containts more partnumbers than only AMS300037, these orders must be ignored in this filter.

Thanks for your help!

This is the set which must be filtered:

Order nr	partnr
8998407	AMS300065
8998407	AMS300037
8998407	AMS900010
8998421	AMS300037
8998421	AC120043303
8998421	AMS900010
8998421	120074500SV
8998429	AMS300037
8998478	AMS300037

This must be the outcome of the filter

8998429	AMS300037
8998478	AMS300037

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not quite sure whether the below picture is what you are looking for.

Please check the sample pbix file's link down below, and the measure.

https://www.dropbox.com/s/8s9wmfeebhzoxgg/jwi.pbix?dl=0 

Result =

VAR groupbyordernr =

FILTER (

CALCULATETABLE (

GROUPBY (

'Table',

'Table'[Order nr],

"@countpartnr", COUNTX ( CURRENTGROUP (), 1 )

),

ALL ( 'Table'[partnr] )

),

[@countpartnr] = 1

)

RETURN

MAXX ( groupbyordernr, 'Table'[Order nr] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[AlexisOlson]

I would try applying the filter ShowOrder is 1 on your visual where

ShowOrder =
VAR partnr = "AMS300037"
VAR ordr = CALCULATE ( SELECTEDVALUE ( Table1[Order] ) )
RETURN
    IF (
        CALCULATE (
            SELECTEDVALUE ( Table1[partnr] ),
            ALL ( Table1 ),
            Table1[order] = ordr
        ) = partnr,
        1,
        0
    )

[Anonymous]

@AlexisOlson This seems to work fine!

Now I have an additional question: if I want the partnumber to be a variable (via a filter or so), is that also possible?

[AlexisOlson]

Sure. Read it in just like I did with ordr.

[Anonymous · Accepted solution]

Hi @AlexisOlson , Can you help me a little bit further please?

I have changed the formula a bit, I am using the REPAIR CODE now.

This works fine, but now I want to be able to change the repair code, using a slicer.

I have already created a slicer with two repair codes, by using a simple table.

Is it now possible to change the VAR REPAIRCODE?

Meaning, If I click on A03 1 RP 26, the measure must work with this code instead of A01 1 RN 26

Thanks!

[AlexisOlson]

VAR repaircode = CALCULATE ( SELECTEDVALUE ( SRS_repair_codes[2_comm_op....Klant] ) )

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not quite sure whether the below picture is what you are looking for.

Please check the sample pbix file's link down below, and the measure.

https://www.dropbox.com/s/8s9wmfeebhzoxgg/jwi.pbix?dl=0 

Result =

VAR groupbyordernr =

FILTER (

CALCULATETABLE (

GROUPBY (

'Table',

'Table'[Order nr],

"@countpartnr", COUNTX ( CURRENTGROUP (), 1 )

),

ALL ( 'Table'[partnr] )

),

[@countpartnr] = 1

)

RETURN

MAXX ( groupbyordernr, 'Table'[Order nr] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

@Jihwan_Kim 

Thanks, works fine!

[AlexisOlson]

Are you trying to do this in the query editor or with DAX?

[Anonymous]

Hi @AlexisOlson 

Preferable with DAX, because this is just an example.

If I know how to do this I am planning to make it workable for other partnumbers as well.

[AlexisOlson]

OK. How are you using this filter? Are you trying to define a calculated table or is it part of a measure or are you trying to limit what is showing in a visual or what?

[Anonymous]

I am trying to limit what is showing in a visual. 

If I select a partnumber via a filter, then the visual is only showing me the data as described above.

Thanks for your help so far.