IF statement with decimal and text filters and another table with date filter

[tadeom0]

Hello DAX experts,

I want to generate a table or column that will filter the data by using text and decimal value. Also I want to filter using Calendar date.
The logic is IF the value is <0 and the text is "Deficit" and the Month is March then included.
If the value is >0 and the text is Deficit and the Month is March then excluded.

Let me know if you encountered this situation befor

[Anonymous]

Thanks for the reply from rajendraongole1.

Hi @tadeom0 ,

You can dynamically select months by creating a calculated table and using the slicer. The steps are as follows, and you can adjust them to suit your needs as well:

1. Create a calculated table:

Month = ADDCOLUMNS(VALUES('Table'[Date]),"Month",MONTH('Table'[Date]))

2. Create a measure:

Measure = 
VAR _month = MONTH(MAX('Table'[Date]))
VAR _table = DISTINCT('Month'[Month])
VAR _select = CONCATENATEX(_table, 'Month'[Month], ",")
RETURN
IF(
    CONTAINSSTRING(_select, _month) && MAX('Table'[Text]) = "Deficit",
    IF(MAX('Table'[Value]) < 0, "included", "excluded")
)

3. Create a slicer using the month field of the calculated table. The result is shown in the figure:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @tadeom0  - You can achieve this in Power BI by creating a calculated column or a measure to filter your data based on the conditions you specified

Filter_Status =
VAR MonthName = FORMAT('YourTable'[Date], "MMMM") -- Extracts the month name
RETURN
IF(
'YourTable'[TextColumn] = "Deficit" && MonthName = "March",
IF('YourTable'[Value] < 0, "Include", "Exclude"),
BLANK() -- Keeps other rows as blank
)

Add a page-level or visual-level filter where Filter_Status = "Include".

Hope this works. please check

[tadeom0]

Is there a way that the month will be dynamic? or can i add more months and year to the selection?