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,
What is the column or measure formula to generate the Y or N in the 'Does the order contain apples' column? Using a DirectQuery model.
OrderID | Item | Does the order contain apples? |
001 | Apples | Y |
001 | Brocolli | Y |
002 | Pears | Y |
002 | Apples | Y |
002 | Bananas | Y |
003 | Bananas | N |
003 | Pears | N |
004 | Apples | Y |
Thanks for your help.
NewColumn=IF(COUNTROWS(FILTER(Table,Table[Item]="Apples"&&Table[OrderID]=EARLIER(Table[OrderID])))>0,"Y","N")
Hi, thanks for your reply, unfortunately I get an error when I try that - 'EARLIER/EARLIEST refers to an earlier row context which doesn't exist'.
my code is to create a calculated column, not a measure
if to create a measure, try this
=IF(CALCULATE(COUNTROWS(Table),Table[Item]="Apples",ALLEXCEPT(Table,Table[OrderID]))>0,"Y","N")
Hi, when I tried the original formula as a column, it said 'Function 'COUNTROWS' is not allowed as part of calculated column DAX expressions on DirectQuery models', and the latter formula as a measure it doesn't error when creating it but says 'can't display the visual' when I add it to a table. Sorry I should of mentioned in my original post I'm using Direct Query, I'll add that in now. So I'm not sure if there is a solve for this using DAX, maybe only SQL?
Hi @rhiheu
Please try
contain apples =
IF (
"Apple"
IN CALCULATETABLE (
VALUES ( 'Table'[Item] ),
ALLEXCEPT ( 'Table', 'Table'[OrderID] )
),
"Y",
"N"
)
Hi @tamerj1, thanks for your reply. I've inputted that formula and it doesn't error, but then when I try and add it to my table it says 'Can't display the visual' - I'm using a Direct Query model if that makes a difference?
@rhiheu
If you are creating a measure, then please clarify the existing filter context.
Hi, If I use your formula for a column it says 'Function 'CONTAINSROW' is not allowed as part of calculated column DAX expressions on DirectQuery models' which is odd since 'CONTAINSROW' is not actually in the formula.
Re: filter context, I'm not sure if I understand the question sorry - I have a date filter applied, but I want this formula to ignore all filters.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |