topic Re: Need Help DAX with Case in DAX Commands and Tips

Need Help DAX with Case

esuryahadi — Mon, 16 Oct 2023 02:14:15 GMT

Hello,

I have a table like this.

ID	Index	Start Date	Finish Date	Days	Status	Alert
1	AW2	01/09/2023	16/11/2023	76	On Going
2	AD3	02/08/2023	16/10/2023	75	On Going
3	AF5	03/08/2023	17/11/2023	106	On Going
4	AH3	04/08/2023	11/10/2023	68	On Going
5	DD1	05/08/2023	05/09/2023	31	Finish

I want to create an alert column with the condition that if the completion date column is less than 30 days from the current date then the alert column is yellow.
If the completion date column is later than today's date then the alert column is red.
However, if the status column is complete then the alert column is black

I ask for input on this matter

Thank you

Re: Need Help DAX with Case

Jihwan_Kim — Mon, 16 Oct 2023 04:20:43 GMT

Hi,

I am not sure if I understood your question correctly about the red yellow color condition, but please check the below picture and the attached pbix file.

One of many ways to achieve this is,

- create a measure [Alert:]
- configure conditional background color

- configure conditional font color that exactly the same as the background color in order not to show the result of the [Alert:] measure.

Alert : = IF ( HASONEVALUE ( Data[ID] ), "Alert" )

Alert rules measure: = VAR _today = TODAY () VAR _result = SWITCH ( TRUE (), SELECTEDVALUE ( Data[Finish Date] ) > _today, "Red", SELECTEDVALUE ( Data[Finish Date] ) > _today - 30, "Yellow" ) RETURN _result

Alert rules font color: = VAR _today = TODAY () VAR _result = SWITCH ( TRUE (), SELECTEDVALUE ( Data[Finish Date] ) > _today, "Red", SELECTEDVALUE ( Data[Finish Date] ) > _today - 30, "Yellow", "White" ) RETURN _result

Re: Need Help DAX with Case

esuryahadi — Mon, 16 Oct 2023 07:14:43 GMT

Thank you in advance and I apologize for any inconvenience. However, there are some discrepancies that need to be addressed. Specifically, ID 2 should be marked in red as it has exceeded the completion date. On the other hand, ID 3 should not remain in white, since its completion date is still far away.

Re: Need Help DAX with Case

Jihwan_Kim — Mon, 16 Oct 2023 13:56:21 GMT

Hi,

I think my understanding of the logic was wrong.

I tried to fix it and please check the below picture and the attached pbix file if it suits your requirement.

Re: Need Help DAX with Case

esuryahadi — Mon, 16 Oct 2023 23:34:55 GMT

thank you so much