cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KhanhTu
Frequent Visitor

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

 

 

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]), "N/A",VC_Ratio)



The visual

KhanhTu_0-1622458611960.png

The meassure:

KhanhTu_1-1622458637767.png

 

Thanks you

Khanh Tu

 

 




2 ACCEPTED SOLUTIONS
selimovd
Super User
Super User

Hey @KhanhTu ,

 

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 regards
Denis
 

View solution in original post

Hey @KhanhTu ,

 

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_visual

 

Or 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_visual

 

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 regards
Denis
 

View solution in original post

4 REPLIES 4
PaulDBrown
Super User
Super User

Try:

YTD visual V/C Ratio =
VAR VC_Ratio = DIVIDE([CV_TOTAL_VOLUME_YTD],[YTD Total cases],0)
Return
IF(MAX(Date Table [year]) < 2019, "N/A",VC_Ratio)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






selimovd
Super User
Super User

Hey @KhanhTu ,

 

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 regards
Denis
 
KhanhTu
Frequent Visitor

Thanks 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 months

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] ) = "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)

RETURN
YoY_YTD_VC_Ratio_visual


Best regards
Khanh

Hey @KhanhTu ,

 

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_visual

 

Or 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_visual

 

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 regards
Denis
 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors