Forum Discussion
How to use ISFILTERED with condition
Hello Power BI Community
I have some trouble to figure out how to use Dax Isfiltered function.
This is my current meassure that calculate YoY expression. When you select categories is shows N/A and this works.
Now I also want to apply isfiltered to MM-YYYY aswell, all before year 2019 should show N/A and Year before works normally with YoY
The visual
The meassure:
Thanks you
Khanh Tu
Hey Anonymous ,
if you want to check for a specific value, you should use the SELECTEDVALUE function.
So you can check if [Case_origin group] or if the selected year is 2019 and then return "N/A" or otherweise return the value:
YTD visual V/C Ratio = VAR VC_Ratio = DIVIDE( [CV_TOTAL_VOLUME_YTD], [YTD Total cases], 0 ) RETURN IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( myDate[Year] ) = 2019, "N/A", VC_Ratio )If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHey Anonymous ,
you could use the "IN" operator:
YoY YTD V/C Ratio Visual = VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1 VAR YoY_YTD_VC_Ratio_visual = IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( DIM_DATE[MM-YYYY] ) IN { "12-2019", "11-2019", "10-2019", "09-2019", "08-2019", "07-2019", "06-2019", "05-2019", "04-2019", "03-2019", "02-2019", "01-2019" }, "N/A", YoY_YTD_VC_Ratio ) RETURN YoY_YTD_VC_Ratio_visualOr check for the year:
YoY YTD V/C Ratio Visual = VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1 VAR YoY_YTD_VC_Ratio_visual = IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || RIGHT( SELECTEDVALUE( DIM_DATE[MM-YYYY] ), 4 ) = "2019", "N/A", YoY_YTD_VC_Ratio ) RETURN YoY_YTD_VC_Ratio_visualIf you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
4 Replies
- selimovd
Most Valuable Professional
Hey Anonymous ,
if you want to check for a specific value, you should use the SELECTEDVALUE function.
So you can check if [Case_origin group] or if the selected year is 2019 and then return "N/A" or otherweise return the value:
YTD visual V/C Ratio = VAR VC_Ratio = DIVIDE( [CV_TOTAL_VOLUME_YTD], [YTD Total cases], 0 ) RETURN IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( myDate[Year] ) = 2019, "N/A", VC_Ratio )If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicThanks you selimovd
Is there better way I can optimize my code, because im using a text column as my date x axis. I cannot use logical operator to filter in syntax.
This is my current code, i have to select all those 12 monthsYoY YTD V/C Ratio Visual =Var YoY_YTD_VC_Ratio = DIVIDE([YTD V/C Ratio],[LY YTD V/C Ratio],0)-1Var YoY_YTD_VC_Ratio_visual =IF(ISFILTERED(SALESFORCE_CASES_FCT_20210429[Case_origin group]) ||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "12-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "11-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "10-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "09-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "08-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "07-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "06-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "05-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "04-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "03-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "02-2019"||SELECTEDVALUE( DIM_DATE[MM-YYYY] ) = "01-2019","N/A",YoY_YTD_VC_Ratio)RETURNYoY_YTD_VC_Ratio_visual
Best regards
Khanh- selimovd
Hey Anonymous ,
you could use the "IN" operator:
YoY YTD V/C Ratio Visual = VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1 VAR YoY_YTD_VC_Ratio_visual = IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || SELECTEDVALUE( DIM_DATE[MM-YYYY] ) IN { "12-2019", "11-2019", "10-2019", "09-2019", "08-2019", "07-2019", "06-2019", "05-2019", "04-2019", "03-2019", "02-2019", "01-2019" }, "N/A", YoY_YTD_VC_Ratio ) RETURN YoY_YTD_VC_Ratio_visualOr check for the year:
YoY YTD V/C Ratio Visual = VAR YoY_YTD_VC_Ratio = DIVIDE( [YTD V/C Ratio], [LY YTD V/C Ratio], 0 ) - 1 VAR YoY_YTD_VC_Ratio_visual = IF( ISFILTERED( SALESFORCE_CASES_FCT_20210429[Case_origin group] ) || RIGHT( SELECTEDVALUE( DIM_DATE[MM-YYYY] ), 4 ) = "2019", "N/A", YoY_YTD_VC_Ratio ) RETURN YoY_YTD_VC_Ratio_visualIf you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
PaulDBrownCommunity Champion
Try:
YTD visual V/C Ratio =VAR VC_Ratio = DIVIDE([CV_TOTAL_VOLUME_YTD],[YTD Total cases],0)ReturnIF(MAX(Date Table [year]) < 2019, "N/A",VC_Ratio)