Measure to select rows based on dynamic conditions

[honkiedonkie]

Hi, I'm relatively new to PowerBI so I have been searching for so many ways to do this but none of them seem to fit. 

I would like to find a way using a measure to select rows based on a condition, and on top of it, select all corresponding rows with a matching category. The catch is that the condition is dynamic.

This will probably be easier to illustrate with the below example.

Suppose I have the below dataset, displayed in a table visual:

Category	Week	Value
A	12	88
A	13	30
A	14	99
B	13	80
B	14	60
B	16	90
B	17	80
C	13	80
C	14	32
D	14	80
D	15	90
D	16	70
D	17	84

Suppose I want to select such that Value < 50. 
My requirement is that, for any row that fulfils the condition, I want to
1. Retrieve that row's category

2. Select all other rows with that category

My desired output will be as follows:

Category	Week	Value	Selected
A	12	88	Yes
A	13	30	Yes
A	14	99	Yes
B	13	80
B	14	60
B	16	90
B	17	80
C	13	80	Yes
C	14	32	Yes
D	14	80
D	15	90
D	16	70
D	17	84

I have indicated in red the rows that match the condition. In this example, since those rows are A and C, all rows with categories A and C are selected. 

Currently, the problem I'm facing is that since Measures only work on a row context, there's no way I can select all other rows with the corresponding categories. Currently, the measure I have looks something like this:

Selected =
VAR N = [Parameter Value] // To be selected by WhatIf Parameter Slicer
RETURN SWITCH(TRUE(),
Value < N, "Yes"
)

As you may be able to tell, this can only get me the exact rows that match the value (Only those in red in the table above). I can't figure out a way to get it to work for other rows with the same category. I've looked at this which is essentially what I need, but it only works for a set value, not a variable, so it would not be able to work dynamically. 

I've thought of ways, such as creating another calculated Table or Column to 'store' the 'Bad' Categories (in the case of this example, A and C) so I can reference to it when selecting. However I have read that calculated Tables are not dynamic and needs to be refreshed manually, so that may not work in my case. Additionally, I can't really utilise Power Query or make direct changes to my dataset as the automated data-uploading process is pretty much already set in stone and my supervisor has requested that direct changes to the dataset be minimised. If there is a way I can do it in just measures, that would be great. 

This is probably the simplest way I can put in words my current problem. If it is truly possible to do the above using measures, it would be a great breakthrough in my work. My background is in programming and not so much in querying language, so learning to navigate DAX has been a challenge. I will be very grateful if anyone can help me with this issue, and will gladly provide more information if needed. Thank you!

[Anonymous · Accepted solution]

Hi @honkiedonkie ,

Please refer to my pbix file.

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

Hi @honkiedonkie ,

Please have a try.

Create 2 measures.

Measure =
VAR _1 =
    SELECTEDVALUE ( 'Table (2)'[value] )
VAR _2 =
    IF ( MAX ( 'Table'[Value] ) <= _1, "Yes", BLANK () )
RETURN
    _2

Measure2 =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
    ),
    [Measure]
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[honkiedonkie]

This also works, thank you!

However I was wondering - if I want to insert the corresponding value into Measure 2 rather than 'Yes', how could I go about it? Eg. for the C rows, it will display 80 and 32 respectively instead of 'Yes'. MAXX will give me the max of all the values corresponding to the same category and I know that that is expected behaviour. Could you advise me on how to go about doing this? Thank you!

[Anonymous]

Hi @honkiedonkie , 

Please try to change the measure2.

Measure2 =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = SELECTEDVALUE ( 'Table'[Category] )
        ),
        [Measure]
    )
RETURN
    IF ( _1 <> BLANK (), MAX ( 'Table'[Value] ), BLANK () )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[honkiedonkie]

That's genius, thank you! 

One more thing, if rather than 'Table'[Value] I had a measure of the value - is there a way I can still do the above? 

In my actual dataset there are some values with repeated Category-Week combination and I have a measure that returns the average of those values for the rows with same combination. 

Averaging Measure = CALCULATE(AVERAGE('Table'[Value]), ALLEXCEPT('Table', 'Table'[Category], 'Table[Week]))

The current way I can go about doing this is to create a calculated column rather than measure, but for a large dataset my computer does not have sufficient memory. I was wondering if there is a way I could use only measures to do this. Thank you again!

[ThxAlot]

Show Info.pbix

[honkiedonkie]

This is genius, thank you for this!

Just one more thing - is there a way for me to do the same but filter using a measure rather than a column? 

Say, if the dataset has multiple rows where Category and Week is the same, and I have a measure that takes the average of all rows with the same Category-Week. Putting FILTER(INFO, [Averaging Measure] < MAX(Threshold[Value])) doesn't seem to work. 

Again, thank you so much!