Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

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

 

 

smpa01_0-1639680469308.pngsmpa01_1-1639680502182.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

16 REPLIES 16
Alvin_Zang
Frequent Visitor

Hi  @pbi_user555 

    

    Is the snapshot  below meets your requirement?

 

   

Alvin_Zang_2-1639673079243.png

 

Anonymous
Not applicable

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 belowAlvin_Zang_0-1639701397739.png

     

  • write a measure

         

Earliest Begin Date = MIN( 'Table2'[Begin_Date] )
Lastest End Date = MAX( 'Table2'[End_Date] )
Anonymous
Not applicable

@Alvin_Zang 

 

Thanks! I will have to test this again, but it's not giving the exact output that I'm looking for. 

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

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
Not applicable

@v-yangliu-msft 

 

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_Column1Filter_Column2Filter_Column3Filter_Column4Values
abc10aaa10001
xyz50bbb10002
abc100ccc50003
abc50aaa10004

 

Assume the second table has the following structure: 

Table2

ValuesBegin_DateEnd_Date
112/15/202112/17/2021
212/14/202112/19/2021
312/16/202112/20/2021
412/17/202112/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.

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

 

 

smpa01_0-1639680469308.pngsmpa01_1-1639680502182.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@Anonymous  did you have a chance to look into it?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Hi @smpa01 

Thank you! This solution is giving the expected output.

 

One question though: say, instead of two tables I just have one table (so all Filter_Columns are in the same table along with the Dates), would the above solution work the same way? Or there will be no need to use TREATAS function?

@Anonymous  it will not work from one single table. You need two tables as the original question and disconnected

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 

I see, thanks for clarifying.

v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

I created some data:

vyangliumsft_2-1638866908066.png

Here are the steps you can follow:

1. Create measure.

Max_Measure = CALCULATE(SUM('Table'[Column2]),FILTER(ALL('Table'),'Table'[Column1]="XYZ"))

2. Result:

vyangliumsft_3-1638866908067.png

 

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
Not applicable

@v-yangliu-msft 

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?

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

Anonymous
Not applicable

@amitchandak 

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. 

No, you can't do with a measure.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.