Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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] )
)
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.
Earliest Begin Date = MIN( 'Table2'[Begin_Date] )
Lastest End Date = MAX( 'Table2'[End_Date] )
Thanks! I will have to test this again, but it's not giving the exact output that I'm looking for.
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.
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:
For example:
Say, there is a slicer for Filter_Column1 and a user sets Filter_Column1 to be "abc", then:
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] )
)
@Anonymous did you have a chance to look into it?
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
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
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 ,
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.
No, you can't do with a measure.
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |