Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |