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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JordanJackson
Advocate II
Advocate II

How to add FILTER to this dax measure

Hi all, 

 

I'm loking for some help with adding in filters to this dax measure:


Score % difference from Target =

IF(
    NOT ISBLANK([Score]),
    DIVIDE(
        [Score] - SUM('Targets'[Ajusted Target (per million)]),
        SUM('Targets'[Ajusted Target (per million)])
    )
)
 
I have Regions (North, South, East, West) and I need a seperate easure filtered to each of these for the project I am working on. I am sure theres a way to just add in Region = 'Northern' but I'm not sure the best way to g about it. 
 
Any help would be greatly appreciated!
Thanks in advance 
1 ACCEPTED SOLUTION

@JordanJackson 

If you want to have the filter directly in the DAX measure please try 

 

 

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
   TABLE[COLUMN]="Value"
   )
)

 

 

OR

 

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
      FILTER(
         ALL(TABLE),
         TABLE[COLUMN]="Value"
      )
   )
)

 

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

6 REPLIES 6
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @JordanJackson 

 

first of all due to performance you should rewrite the formula like this to save caluclation time:

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
    DIVIDE(
        [Score] - __SUM,
        __SUM
    )
)

 

Regarding the filter in general there are 100 ways for filtering.

you can filte rin the measure using CALCULATE function. But as you describe it yoi should use a slicer or a visual filter

Slicers in Power BI - Power BI | Microsoft Learn

Format filters in Power BI reports - Power BI | Microsoft Learn

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@JordanJackson 

If you want to have the filter directly in the DAX measure please try 

 

 

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
   TABLE[COLUMN]="Value"
   )
)

 

 

OR

 

Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])

RETURN

IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
      FILTER(
         ALL(TABLE),
         TABLE[COLUMN]="Value"
      )
   )
)

 

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

I'll change over the Dax to the more efficent one. 

I need to filter to be in the DAX as this is for having dynamic values and I havn't seen a way to add a filter to these values so having a measure for each region seems to be the necesary soloution. 

 

Thankyou for your help!

@JordanJackson 

I think I made a mistake.

 

The measure has to be like this:

Score % difference from Target =

IF(
    NOT ISBLANK([Score]),
   CALCULATE(

       VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
       RETURN

       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
   TABLE[COLUMN]="Value"
   )
)

 

or this

Score % difference from Target =
IF(
    NOT ISBLANK([Score]),
   CALCULATE(
       VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
       RETURN

       DIVIDE(
           [Score] - __SUM,
           __SUM
       ),
      FILTER(
         ALL(TABLE),
         TABLE[COLUMN]="Value"
      )
   )
)

 

Can you please try and verify?

 

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

I've used the second one from your revised ones and its all working, thankyou for your help!

Great! Thakn you for the feedbck! 🙂

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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