Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi. I have this mock data table
project | Findings |
x | 1 |
x | 1 |
x | 1 |
x | 1 |
y | 1 |
y | 2 |
y | 1 |
y | 1 |
z | 1 |
z | 2 |
z | 1 |
I want the result to be the project ID that has only value =1. In this case, this should be project X. I will use this in count card and bar graph. Thanks!
Hi @Fair-UL
Try this measure to achieve the project ID with only value 1.
Project_ID =
CALCULATE (
IF (
MINX ( Project, IF ( SELECTEDVALUE ( Project[Findings] ) = 1, 1, 0 ) ) = 1,
SELECTEDVALUE ( Project[Project] ),
BLANK ()
),
ALLEXCEPT ( Project, Project[Project] )
)
Or another way in case you need, create calculated columns to count the number of values for each project and mark whether a project has only one value 1. In this way, you can use filters/slicers or other methods to filter the projects with only value 1.
Count Of Values =
CALCULATE (
DISTINCTCOUNT ( Project[Findings] ),
FILTER (
Project,
Project[Project]
= EARLIER ( Project[Project] )
)
)
Has Only Value 1 =
IF (
AND ( Project[Count Of Values] = 1, Project[Findings] = 1 ),
"Yes",
BLANK()
)
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Fair-UL,
This may not be the best solution but it achieves what you want:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUTJUitUhxKpEYRlhEYOwqlBYRshisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column1", Order.Ascending}, {"Column2", Order.Descending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Column2] = 2 then "Exclude"
else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
#"Filled Down"
You can count the number of such projects like this:
CountProjectsWithOnly1Findings =
COUNTROWS (
FILTER (
VALUES ( MockData[project] ),
CALCULATE ( SELECTEDVALUE ( MockData[Findings] ) ) = 1
)
)
Note that SELECTEDVALUE throws a blank if there are multiple values.
I tried this solution but it gives me all projects with value 1. I need projects with value=1 ONLY (meaning that if one project has both 1 and 2, I do not want it to be counted.
It is like I donot want anything in the intersection area to be counted. I hope this is clear now.
Thanks for the quick response and the DAX
If a project has both 1 and 2, then SELECTEDVALUE throws a blank and the condition should fail.
If the formula isn't working, it might be that you are trying to use it in a context that doesn't include all of the rows associated with a project. For example, it probably won't work like you expect if you try to use it in a calculated column.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |