Forum Discussion

Ethanhunt123's avatar
Ethanhunt123
Icon for Helper IV rankHelper IV
1 year ago
Solved

Measure as a slicer

I have three tables in my Power BI model:

  • Sales Order Fact

  • Order Date

  • Customer

All these tables are properly connected.

I’ve created a few DAX measures based on them:

  1. Number of Customers with Order amount > $100

  2. Number of Customers with Order amount ≤ $100

The amounts are grouped Year-to-Date (YTD). So if the user selects August, the total amount is calculated from January 1st to August 31st.

All calculations are working correctly. However, I now need to create a slicer to allow users to filter between "> $100" and "≤ $100". This is where I'm stuck.

I created a measure-based flag, but I have to manually apply the filter to each visual — and this report has many visuals. Also, I’m unable to apply the measure as a filter on Card visuals, which is a limitation.

Is there any alternative approach to creating a slicer that dynamically filters visuals based on "> $100" or "≤ $100" orders?

 

 

Expected output 

The Customer table contains fields such as Name, Address, and customer Number.
The Order Date table is a standard date dimension with fields like Order Date and Month, Year.
The Sales Order Fact table includes details like Order Amount, Quantity, and other transaction-level data.

The report includes multiple visuals — such as cards, tables, bar charts, and several slicers.

I want to implement a filter where:

If the user selects "> 100", the report should display data only for customers whose total order amount is greater than $100.

If the user selects "≤ 100", the report should show data only for customers whose total order amount is $100 or less.

This filter should apply across the entire report to all visuals consistently.

 

  • Create a disconnected slicer table with values "> 100" and "≤ 100"
    Use it as a slicer in your report
    Write a DAX measure that checks the selected slicer value and flags customers based on YTD order amount
    Apply that flag measure as a filter (= 1) on all visuals — including cards, tables, and charts

7 Replies

  • Please specify more the content of each table you have and show the data model connections

     

    What should happen if I select >100? What the >100 should filter? And in what table?

     

    I would solve in this way

     

    1 create a table with one column and two rows

    <100

    >100

     

    This table will be not connected

     

    If I select nothing or all the values of the slicer you see everything you see now

     

    Otherwise you will see what you specify

     

    What do you think? Can tou provide sanples of the tables so I create the pbix?

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

     

     

     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

    • Ethanhunt123's avatar
      Ethanhunt123
      Icon for Helper IV rankHelper IV

      The Customer table contains fields such as Name, Address, and customer Number.
      The Order Date table is a standard date dimension with fields like Order Date and Month, Year.
      The Sales Order Fact table includes details like Order Amount, Quantity, and other transaction-level data.

      The report includes multiple visuals — such as cards, tables, bar charts, and several slicers.

      I want to implement a filter where:

      If the user selects "> 100", the report should display data only for customers whose total order amount is greater than $100.

      If the user selects "≤ 100", the report should show data only for customers whose total order amount is $100 or less.

      This filter should apply across the entire report to all visuals consistently.

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support

    Hi Ethanhunt123,

    can you share a sample of the data in a pbix as our super user requested as this will make us easier to resolve issue your facing.

     

     

     

    thanks,

    Prashanth Are

    MS Fabric communitry support

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support

    Hi Ethanhunt123,

    we are reaching out to confirm whether your query got resolved or you still need any help here.

     

     

    Thanks,

    prashanth

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion

    Create a disconnected slicer table with values "> 100" and "≤ 100"
    Use it as a slicer in your report
    Write a DAX measure that checks the selected slicer value and flags customers based on YTD order amount
    Apply that flag measure as a filter (= 1) on all visuals — including cards, tables, and charts