Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello guys,
I am trying to create a measure TotalExaminationBacklog which counts all the examinationsIDs with the status WAI, VER, APP, HEL and SCH.
But it seems that my measure (see image below) doesnt give any result. any suggestions?
Thanks !
Solved! Go to Solution.
Hey,
my current favorite to check if one value is contained in a set of values is using the newer IN() operator
So you can write it like this
COUNTX( 'Yourtable' ,'Yourtable'[YourColumn] IN ("WAI", "VER", "APP","HEL", "SCH") )
Edited 2017-11-09 (start):
The statement above will not work due a type, the correct statement uses curly braces, why is explained in my last post. The correct statement will look like
COUNTX( 'Yourtable' ,'Yourtable'[YourColumn] IN {"WAI", "VER", "APP","HEL", "SCH"} )
Edited 2017-11-09 (end):
Assuming that the STATUS comes from a slicer, this would also be possible
COUNTX( 'Yourtable' ,'Yourtable'[Yourcolumn] IN (ALLSELECTED('MaybeAnotherTable'[ColumnUsedInSlicer])) )
Maybe this idea helps you give another idea
Hi Tom!!
Thank you for all your contributions to this site. I was wondering if you can help me. I have tried
Hey @Anonymous ,
maybe this measure will provide what you are looking for:
TotalFires =
SUMX(Query1,
IF(Query1[INCIDENT_CATEGORY] IN
{"Accidental Dwelling Fire",
"Accidental Non Domestic Fire",
"Accidental Other Primary Fire",
"Accidental Vehicle Fire",
"Accidental Small Fire",
"Chimney Fire",
"Deliberate Dwelling Fire",
"Deliberate Non Domestic Fire",
"Deliberate Small Fire",
"Deliberate Other Primary Fire",
"Deliberate Vehicle Fire"}
, 1 , BLANK())
)
If this does not solve your problem, please start a new question.
The error is due to the fact that COUNTX is not able to count BOOLEANs, a boolean is returned by column IN {...}
Regards,
Tom
Hi Tom,
I have come across a similar problem and your above solution works perfect for me. If wanted to use the above formular to filter by column 1 (Text values) and an additonal columns (Text values) how would that work?
For example
I want to sum column 1
Filter by column 2 (text values)
and filter by column 3 (text values)
Hope this makes sense.
Thank you for this answer - specifically related to using "in ("value", "value", "value", . . .)".
I was struggling to find an alternative to using || and "or"
I did notice in my query I needed to modify the syntax by using a curly bracket because the system would not accept the parentheses:
4_Stage_Count = CALCULATE(COUNT(Opportunities[AccountId]),Opportunities[Stage] in {"Closed Won", "Closed Lost"})
Let me know if anyone knows why the () had to be replaced by the {}.
Hey,
basically my first post contains a typo (I have been in a hurry).
IN
searches in column-table,
The most simple form to define a table with just one column is to use {"curly", "braces"}. This little example creates a table with on column and two rows. Pleas be aware that the table is defined w/o a table name and w/o a name for the column.
I will edit my post immediately.
Regards
Tom
i just have the solution for this case..
Measure 3 = CALCULATE([TotalExaminations];
Examinations[exa_StatusID] = "WAI" ||
Examinations[exa_StatusID] = "VER" ||
Examinations[exa_StatusID] = "APP" ||
Examinations[exa_StatusID] = "HEL" ||
Examinations[exa_StatusID] = "SCH" )
Thanks anyway guys!
Thanks for sharing the solution and it resolved my needs.
Hello @Anonymous,
thanks for your response.
unfortunatly the given ideas dont work 😞
please check the image below
Hey, thanks for this, what if you want to do the opposite, you need to select values you DONT want in your results, how would that look like? thanks!
Solved my problem :). Thank you!
When filtering on the ID's try the following:
OR(
Explanations[StatusID] = "WAI",
Explanations[StatusID] = "VER",
Explanations[StatusID] = "APP", etc.
)
I think the way you have it, you are concatenating all the values into 1 string, which doesn't exist.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |