topic Re: Need help on dax, to satisfy the second condition even if the condition met in the first condition. in DAX Commands and Tips

Need help on dax, to satisfy the second condition even if the condition met in the first condition.

Heena_9980400 — Mon, 28 Oct 2024 13:50:58 GMT

Hello Team,

I'm reaching out for assistance with a requirement that I’ve been working on but haven't been able to fully achieve. Here’s the breakdown of what I need to implement:

The goal is to categorize data based on:

Last 15 days
Last 30 days
More than 30 days (Other than Last 30 Days)

While I was able to get the Last 15 Days and Other than Last 30 Days categories working correctly, I’m encountering an issue with the Last 30 Days. Instead of displaying data from the full 30-day period, it’s only returning the previous 15 days due to the condition for the Last 15 Days already being met. As a result, my Last 30 Days category is only showing the 15 days prior to the Last 15 Days, rather than the entire 30-day period.

I've attached the report link for reference. Your insights or suggestions on how to structure the logic to capture all 30 days accurately would be greatly appreciated!

https://app.powerbi.com/groups/me/reports/0d35fcaa-dcad-4f28-a35b-3314c1bfa035?ctid=76a2ae5a-9f00-4f6b-95ed-5d33d77c4d61&pbi_source=linkShare

Thank you for your help.

Re: Need help on dax, to satisfy the second condition even if the condition met in the first condition.

saud968 — Mon, 28 Oct 2024 14:52:30 GMT

Here is I can think for approaching

Last 15 Days:
Last15Days =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Date] >= TODAY() - 15 && 'YourTable'[Date] <= TODAY()
)
)

Last 30 Days:
Last30Days =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Date] >= TODAY() - 30 && 'YourTable'[Date] <= TODAY()
)
)

More than 30 Days:
MoreThan30Days =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Date] < TODAY() - 30
)
)

To ensure that the “Last 30 Days” measure includes the full 30-day period and is not affected by the “Last 15 Days” measure, you can use these measures in your visualizations or calculations without them interfering with each other.

If you need to categorize your data into these groups within a single column, you can create a calculated column like this:

DateCategory =
SWITCH(
TRUE(),
'YourTable'[Date] >= TODAY() - 15, "Last 15 Days",
'YourTable'[Date] >= TODAY() - 30, "Last 30 Days",
'YourTable'[Date] < TODAY() - 30, "More than 30 Days"
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

Re: Need help on dax, to satisfy the second condition even if the condition met in the first condition.

Jihwan_Kim — Tue, 29 Oct 2024 04:18:26 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = VAR _t = ADDCOLUMNS ( FILTER ( 'calendar', 'calendar'[Date] <= TODAY () ), "@sales", CALCULATE ( SUM ( sales[sales] ) ) ) VAR _category = FILTER ( _t, 'calendar'[Date] <= TODAY () - MIN ( category[min] ) && 'calendar'[Date] >= TODAY () - MAX ( category[max] ) ) RETURN SUMX ( _category, [@sales] )

Re: Need help on dax, to satisfy the second condition even if the condition met in the first condition.

Heena_9980400 — Tue, 29 Oct 2024 04:55:29 GMT

Hi Jihwan_Kim ,

Thanks for responding.
actually im trying to get 3 buttons in a slicer, last 15 days, last 30 days and other than last 30 days.

in this process, the last 30 days arent giving full 30 days date.

in_last =

IF (

DATEDIFF ( etest[Date], TODAY(), DAY ) <= 15,

"Last 15 days",

IF (

DATEDIFF ( etest[Date], TODAY(), DAY ) <= 30,

"Last 30 days",

"Other than last 30 days"

)

)

hope you got my problem statement.

Re: Need help on dax, to satisfy the second condition even if the condition met in the first condition.

Jihwan_Kim — Tue, 29 Oct 2024 05:01:41 GMT

Hi,

Thank you for your message, and I am not 100% sure in your case (I think you are trying to create a calculated column), but when trying to use IF DAX function in here, and if the first condition is met, the second condition cannot include the items that are already belong to the first condition.