Forum Discussion
Get column values based on condition in another column
Hello,
I'm trying to get values in a column that meet a condition in another column. For example, say I have two columns in a table, and I want to get all the values in column2 for which column1 value = "XYZ". So far, I've been able to get only the first or last values in column2 using CALCULATE with FIRSTNONBLANK or LASTNONBLANK functions, however, when I try getting all of the values in column2 it gives a 'multiple values were supplied' error.
Thanks in advance.
Anonymous you can use following two measures
_earliestBeginDate = CALCULATE ( MIN ( Table2[Begin_Date] ), TREATAS ( SUMMARIZE ( Table1, Table1[Values] ), Table2[Values] ) ) _latestEndDate = CALCULATE ( MAX ( Table2[End_Date] ), TREATAS ( SUMMARIZE ( Table1, Table1[Values] ), Table2[Values] ) )
16 Replies
- amitchandak
Super User
Anonymous ,
You can create a new column
Column 3= if([Column1] = "XYZ", [Column2] , Blank() )
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Thanks! That function worked for me.
Is it possible to do the same with a measure instead of a calculated column? Or does a measure output always have to be one value instead of a list of multiple values? Looks like it's not working with a measure for me.
Hi Anonymous ,
I created some data:
Here are the steps you can follow：
1. Create measure.
Max_Measure = CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Column1]="XYZ"))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous
Thanks! However, I'm working with text data and this formula doesn't work in my case and I'm running into the same issue where it's not giving any output with the error message saying that multiple values were supplied. Can this same (or a similar) measure formula be applied for a case where both columns are text data?
Anonymous
Here is a more specific description of what I'm trying to do:
I have two tables. Assume the first table has the following structure:
Table1
Filter_Column1 Filter_Column2 Filter_Column3 Filter_Column4 Values abc 10 aaa 1000 1 xyz 50 bbb 1000 2 abc 100 ccc 5000 3 abc 50 aaa 1000 4 Assume the second table has the following structure:
Table2
Values Begin_Date End_Date 1 12/15/2021 12/17/2021 2 12/14/2021 12/19/2021 3 12/16/2021 12/20/2021 4 12/17/2021 12/18/2021 My goal is to do the following:
Whenever a filter is selected for either of the Filter_Columns (so, any time a filter changes), then do the following:
- First, figure out a list of Values from Table1 that meets the filter selection criteria
- Then, figure out the begin and end dates for the list of these Values from Table2
For example:
Say, there is a slicer for Filter_Column1 and a user sets Filter_Column1 to be "abc", then:
- Query Table1 to get a list of values that meets this filter selection, which will be: 1,3 and 4
- Then, get the earliest begin date and the latest end date in Table2 for this range of these values which will be 12/15/2021 (since it's the earliest date) and 12/20/2021 (since it's the latest date)
Thanks in advance.
smpa01Community Champion
Anonymous you can use following two measures
_earliestBeginDate = CALCULATE ( MIN ( Table2[Begin_Date] ), TREATAS ( SUMMARIZE ( Table1, Table1[Values] ), Table2[Values] ) ) _latestEndDate = CALCULATE ( MAX ( Table2[End_Date] ), TREATAS ( SUMMARIZE ( Table1, Table1[Values] ), Table2[Values] ) )
Hi Alvin_Zang
Yes, so essentially the function first queries Table1 to get the list of values that meet the filter selection criteria, and then queries Table2 to get the earliest start date and latest end date for the list of those values (the end result is one start date and one end date).
Clear. I got another simple solution.
- make a many to many relation like the snapshot below
- write a measure
Earliest Begin Date = MIN( 'Table2'[Begin_Date] )Lastest End Date = MAX( 'Table2'[End_Date] )- make a many to many relation like the snapshot below
