Forum Discussion

herwet's avatar
herwet
Frequent Visitor
4 years ago

How can I only filter one region

Hi, 

 

I have a DAX challenge im wondering if anyone can help me with. 

The case: 

I am visualizing data for each region. Each region consists of multiple profit centers. In region ACON, I want only some of the profit centers. so far I have this code: 

I have specified what profit center I want to include in ACON. However, now the filter is applying to all regions. I want it only to filter the ACON region. 

Hope this made some sort of sense and that there are some smart people that have a solution. Thanks 🙂

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi herwet 

    Thanks for reaching out to us.

    In your code, the filter conditions have been written in the fixed area. So if you want only to filter the ACON region, you can use slicer. 

    (1) create the slicer

    (2) create the measure below

    test = IF(ISFILTERED(Slicer[slicer]),IF(MIN('Fact Table'[Region u/ACON]) in ALLSELECTED(Slicer[slicer]),"true","false"),"")

    result

    Best Regards,

    Community Support Team _Tang

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

    • herwet's avatar
      herwet
      Frequent Visitor
      4 years ago

      Hi  v-xiaotang, thank you for responding 😊 
      I would like to visualize the data in a clustered column chart to compare the different regions and their performance 2021 vs 2022.

       

      This means I need to make a measure for cost reimbursement for 2021 and one for 2022. The issue is that Cost reimbursement for 2021 has some other profit centers than 2022, so I would need to specify these profit centers in the measure. 

       

      Do you know how I can write my DAX for this?

      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi herwet 

        Thanks for your reply.

        Usually we need to create a calendar table, put date column from calendar table into table visual, then create the measures like below, 

        total= calculate(sum(fact_table[value]),filter(all(fact_table),year(fact_table[date])=min(calendar[year])))

         

        If Cost reimbursement for 2021 has some other profit centers than 2022, just make sure each record of those profit centers has date 2021-xx-xx, then we can find them by expression year(fact_table[date])=min(calendar[year]), finally get the correct total.

         

        Best Regards,

        Community Support Team _Tang

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