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

Add a filter to a DAX measure

I have 2 Measures that help me count the Job Description products (Generics + Uniques) from my all position report in the SJD column:

 

DAX Measure to count unique values once in the column without blanks (the Generic products)

# SJD Products = DISTINCTCOUNTNOBLANK('All Pos'[SJD #])

 

And

DAX Measure to count blanks (the unique products have no SJD#)

Unique Product SUM = COUNTBLANK('All Pos'[SJD #])

 

# of Products

# Products = [# SJD Products]+[# Unique Products]

 

Now I am being asked to filter this by the "Conversion?" column "CT Con" results.

I already have a filter on the visual so that option is out!

 

My question is can I add a filter to the measure above "# of products" or

do I need to write a new measure?

 

Thanks in advance for your help.

SJD #Conversion?
RAXA0411CT Con
RAXA0411CT Con
BlankCT Con
GNAS0500CT Con
RAXA0411PA Con
BlankCT Con
This would be counted as 4 products 
  
  
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

All Pos:

a1.png

 

You may create a measure as below.

# of Products = 
CALCULATE(
    DISTINCTCOUNT('All Pos'[SJD #]),
    FILTER(
        ALL('All Pos'),
        [Conversion?]="CT Con"&&
        [SJD #]<>""
    )
)+
CALCULATE(
    COUNT('All Pos'[SJD #]),
    FILTER(
        ALL('All Pos'),
        [Conversion?]="CT Con"&&
        [SJD #]=""
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

All Pos:

a1.png

 

You may create a measure as below.

# of Products = 
CALCULATE(
    DISTINCTCOUNT('All Pos'[SJD #]),
    FILTER(
        ALL('All Pos'),
        [Conversion?]="CT Con"&&
        [SJD #]<>""
    )
)+
CALCULATE(
    COUNT('All Pos'[SJD #]),
    FILTER(
        ALL('All Pos'),
        [Conversion?]="CT Con"&&
        [SJD #]=""
    )
)

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

TomMartens
Super User
Super User

Hey @Anonymous ,

 

I'm not sure if I understand your reqirement 100%, but from the measures you already created you can wrap a CALCULATE around the existng measures and create a new one:

# SJD Products 
= 
CALCULATE(
    DISTINCTCOUNTNOBLANK('All Pos'[SJD #])
    , 'All Pos'[Conversion?] = "CT con"
)

Using CALCULATE allows to alter the existing filter context.

 

If you use the column 'All Pos'[Conversion?] as a slicer and select "CT con", then your existing measures will already reflect what you are looking for.

 

Basically the question if you are in need for new measures boils down to - it depends. If you want the measure to always reflect "CT con", no matter if there is a slicer selection, a column/row header, or an axis label, then you need new measures, if you can achieve what you are looking with the existing measures than everything is fine.

 

A last remark, measures are impacted by slicer selection, a column/row header, or an axis label, as implicit filters are created. Measure can adhere to these measures, like the ones you already created, or you can use CALCULATE to alter the filter context.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi Tom, tks for replying.

I apologize for not explaining the requirment accuratly.

I am interested in exploring your solution using CALCULATE.

How would you add a filter to count only "CT Con" from "Conversion" in the following measure

 Title = CALCULATE([# SJD Products]+[# Unique Products] 

This is the measure the I would rather use as it counts both my products together.

 

Thanks in advance.

@Anonymous ,

You can add a filter in the measure if you dont want to show any other values from "Conversion?" column in the visual.

Let me know if you need help with the DAX.

 

Mark this as a solution if I have helped solve your issue.

Veena Shenolikar

Anonymous
Not applicable

Hi Veena, tks for replying.

Yes I only need to count the "CT Con" results from the "Conversion?" column. 

Your help would be appreciated on how to add a filter to this measure.

# Products = [# SJD Products]+[# Unique Products]

 

Thanks in advance.

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.