cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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.

@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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors