Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

View solution in original post

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_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
Community Champion
Community Champion

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 @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 regards
Denis
 
Anonymous
Not applicable

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 @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_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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.