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
I'm new to PowerBI and trying to figure out what I think is a simple formula.
I want to return TRUE if a column value is in a list of values.
i.e.
IF table.field IN ("A","D","G") THEN "Yes" ELSE "No"
As you can see from my example, I don't think in PowerBI language,
How do you do a simple statement like thie in PowerBI?
Thanks
Solved! Go to Solution.
Check out this article, From SQL to DAX: IN and EXISTS:
http://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/
Thanks for the help. This told me what I needed.
The SQLBI website was helpful for me to transition from a SQL syntax to a DAX syntax.
The SWITCH function seems like the most likely tool I'll use.
If DAX doesn't have an IN function, which seems so basic to me, are there many other surprises I should expect as I learn DAX?
Hi @Anonymous,
I agree with @ashishrj. I think that your best option is to use the SWITCH function.
#I'M Not An Expert#
In the absence of an actual IN function, an alternative solution would be to set up a dimension table, its more work at the outset but potentially easier to work with and gives more flexibility
You would just need the dimension table to look something like:
table.field | Lookup
A | Yes
B | No
C | No
D | Yes
then link to your fact table.
If you don't want to list all the possible values then you could just include the matching ones and set up a measure to turn null
into "No"
using this approach you can also have other levels of aggregation to suit different needs
table.field | Lookup | Lookup2
A | Yes | Include
B | No | Include
C | No | Exclude
D | Yes | Exclude
Check out this article, From SQL to DAX: IN and EXISTS:
http://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/
@Anonymous You can try out using multiple if statement...Eg if(value="A","Yes,if(value="D","Yes",...),"No")
or you can also use switch
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 |
---|---|
112 | |
108 | |
101 | |
93 | |
69 |
User | Count |
---|---|
172 | |
135 | |
132 | |
101 | |
95 |