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

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, @o0Chris0o 

 

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, @o0Chris0o 

 

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

TomMartens
Super User II
Super User II

Hey @o0Chris0o ,

 

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

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.

veenashenolikar
Helper V
Helper V

@o0Chris0o ,

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

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.