Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to do a simple IF Statment but it doesn't seem to work; I'm trying to created a calculate column to use as a customer filter.
I'm looking at 3 Columns as listed below:
In the below screen shot the highlighed "Exclude" should be "Include".
Any help is always appricated.
Thanks
Dobby Libr3
Solved! Go to Solution.
Hi @Anonymous
Do you check my answers above?
these are all measures instead of columns
max selected = MAX('calendar'[Date])
Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")
Best Regards
Maggie
Hi @Anonymous
Hi Maggie,
Still not resolved, I have tired out all the Measures in the chat and also looked at the model, I can't figure out why its not picking up, Also my data set has less than 20,000 line and the refresh on visuals takes far too long for the simple calculations.
there is a picture of my model, date table is not connected...
Hi @Anonymous
Do you check my answers above?
these are all measures instead of columns
max selected = MAX('calendar'[Date])
Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")
Best Regards
Maggie
Hi,
This worked after testing again! Thank you very much.
Regards
Dobby Libr3
Hi,
try the following measure:
Report Filter =
IF (
MIN ( 'dwh InventoryTenancy'[Start] )
> CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) &&
MIN ( 'dwh InventoryTenancy'[End] )
<= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ),
"Include",
"Exclude"
)
Regards FrankAT
Hi @FrankAT,
thanks for your input, I currently getting the insufficent memory error, ( I have 32gb RAM and am connecting to a datawarehouse) so I don't think its really a memory issue.
Hi @Anonymous ,
Slicers cannot be used in calculated columns. Instead, you can use measure.
Date_Selected = CALCULATE(MAX('DATE'[Date]),ALLSELECTED('DATE'))
ReportFilter =
VAR selectedDate = 'Date'[Date_Selected]
VAR result =
CALCULATE (
IF (
selectedDate > SELECTEDVALUE ( Inventory[Start] )
&& selectedDate <= SELECTEDVALUE ( Inventory[End] ),
"Include",
"Exclude"
)
)
RETURN
result
If required you apply a visual level filter.
Regards,
Nandu Krishna
Hi @nandukrishnavs ,
Unfortantly this bough back the same results I got where it seems to not be applying the logic.
Thanks
Rob
Hi @Anonymous
It seems your slicer set as "before",
so the range you select is date 2017/10/1~2018/9/1
For the first line 2018/8/1~2018/11/12, it is not include in the selected date range.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
are your Start and End date true calendar dates? If they are text it doesn't work!
Regards FrankAT
Hi @FrankAT
Yeah these are for sure Calender dates!
@v-juanli-msft I tried changing the date slicer but this returned the same result! also this shouldn't make a diffrence as the Dax is going off the [Date_Selected} Column not the acutal slicer...
Thanks
Rob
Hi @Anonymous
I don't know what causes your problem.
On my side, if i have tables without relationships, then create measures
add "date" from "calendar" table to the slicer,
max selected = MAX('calendar'[Date])
Measure = IF([max selected]>MAX([start])&&[max selected]<=MAX([end]),"Include","exclude")
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
try this?
Report Filter =
var _selectedDate = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
var IncExc = if( _selectedDate > calculate(firstdate('Table'[from]),'Table')
&& _selectedDate <= calculate(firstdate('Table'[to]),'Table'),
"Include",
"Exclude"
)
return IncExc
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |