Which Filter function to use to ignore the slicer filter and consider query filter in DAX

[tanisha_10]

Which Filter function to use to ignore the slicer filter and consider the query filter for the same attribute in DAX expression? For one particular case I need to consider "version_text" from the query not from the slicer filter. I tried REMOVEFILTER and ALL filter function but none is giving me correct results on this. Kindly suggest. Thanks.

Output_Mfg_Cost =
VAR Vers = RIGHT ( SELECTEDVALUE ( 'Global SMA'[version_text] ), 4 )
VAR V1_Ver = SELECTEDVALUE ( 'Global SMA'[version_text] )
RETURN

If( Vers="LIVE",
CALCULATE(DIVIDE(SUM('Global SMA'[total_mfg_cost]),1000,0),REMOVEFILTERS('Global SMA'[version_text]),'Global SMA'[version_text]="Jan'24 QCR" && 'Global SMA'[Milestone]="OUTPUT"),

CALCULATE(DIVIDE((SUM('Global SMA'[total_mfg_cost])),1000,0),'Global SMA'[version_text]=V1_Ver && 'Global SMA'[Milestone]="OUTPUT")
)

[v-kaiyue-msft · Accepted solution]

Hi @tanisha_10 ,

A slicer is a Power BI visual used as an alternative to filtering other visuals in a Power BI dashboard. When you add a slicer to a report page in Power BI, the default behavior when checking any slicer value is to filter all related visuals. To clear filtering, you can create a table that has no relationship to the original table, or use edit interactions to exclude the visual.

For more details please refer to the link:
How To Exclude Slicer Selection from the result with DAX in Power BI (antmanbi.com)
How To Ignore Slicer Filter In Power BI? (devoworx.net)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[PijushRoy]

Hi @tanisha_10 

Please try below DAX

Output_Mfg_Cost =
VAR Vers =
    RIGHT ( SELECTEDVALUE ( 'Global SMA'[version_text] ), 4 )
VAR V1_Ver =
    SELECTEDVALUE ( 'Global SMA'[version_text] )
RETURN
    IF (
        Vers = "LIVE",
        DIVIDE (
            CALCULATE (
                SUM ( 'Global SMA'[total_mfg_cost] ),
                REMOVEFILTERS ( 'Global SMA'[version_text] ),
                'Global SMA'[version_text] = "Jan'24 QCR"
                    && 'Global SMA'[Milestone] = "OUTPUT"
            ),
            1000,
            0
        ),
        DIVIDE (
            CALCULATE (
                SUM ( 'Global SMA'[total_mfg_cost] ),
                'Global SMA'[version_text] = V1_Ver
                    && 'Global SMA'[Milestone] = "OUTPUT"
            ),
            1000,
            0
        )
    )

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[tanisha_10]

@PijushRoy  Thanks Pijush for a quick response 🙂
I think the DAX still behaves the same. Actually this DAX is working perfectly fine in a card visual and bar graph but not in a table visual, giving blank value for "output_mfg_cost" while displaying it along with other metrics in the tabular visual . In card visual/bar graph, you see the correct number. I think it gets affected by other filters in the table. Need to understand the row context here, how the filters are working in table visual. Thanks.

[PijushRoy]

Hi @tanisha_10 

If you bring the measure in Table,
The visual Row context always filter the Measure. If you want to remove table row context, you need to use REMOVEFILTER for ALL.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[tanisha_10]

@PijushRoy Yes, that's true. To remove the table row context, we need to use REMOVEFILTER or ALL.  I tried with ALL filter function but that did not work as well. But when I removed 2 particular date fields from the table visual, it showed me the correct result for the measure i.e. $852, no blank value.

Still wonderig how to handle this row context in table visual.Thanks.

IF (
        Vers = "LIVE",
        DIVIDE (
            CALCULATE (
                SUM ( 'Global SMA'[total_mfg_cost] ),
                ALL ( 'Global SMA'[version_text] ),
                'Global SMA'[version_text] = "Jan'24 QCR"
                    && 'Global SMA'[Milestone] = "OUTPUT"
            ),
            1000,
            0
        )