Deal with closed centers in Power BI

[mlsx4]

Hello everyone!

I have got a report with around 20 centers. My problem is that from last year to this one, some of the centers have closed. 

I don't know if it possible to deal with it as I have in mind but, my idea is that if I select year 2024, the slicer must show only the centers that are currently active. If I select year 2023, all of them must appear.

I have been trying to find an approach, but I'm stuck.

Center table is as follows:

Center code	Name	Location
01	Center 1	Spain
02	Center 2	UK
03	Center 3	France

Any ideas will be welcomed

[v-tangjie-msft]

Hi @mlsx4 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  You need to add a status column to tell whether the center is active or not.

(2) We can create a slicer table and a flag measure. 

SlicerData = DATATABLE ( 
    "Year", STRING, 
    {
        { 2023},
        { 2024 }
    }
) 

Flag = SWITCH(TRUE(),
ISFILTERED('SlicerData'[Year])=FALSE(),1,
SELECTEDVALUE('SlicerData'[Year])=2023 ,1,
SELECTEDVALUE('SlicerData'[Year])=2024 && MAX('Table'[State])="active",1,0)

(3) Place [Flag=1] on the visual object and then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[mlsx4]

Hi @v-tangjie-msft 

That's what I was thinking of implementing. But, my problem is how to add state column in such a way that I could deal with dates... Centers comes from the ERP. I'm afraid I would need to do it in an auxiliary excel or I won't be able to deal with it, isn't it?

[v-tangjie-msft · Accepted solution]

Hi @mlsx4 ,

You are right, a status column needs to be added to the data source.

Best Regards,

Neeko Tang

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

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

You example data does not have any dates. It is therefore impossible for us to determin which Centres are 2023 or 2024.

Provide the proper example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confussion.

Rename columns to user friendly names.
Also provide the example desired output, with a clear description of the process flow.
Remember not to share private data ... we don't want you to get into trouble. 😧
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[mlsx4]

Hi @speedramps 

My explanation is not vaugue... It is exactly what I have. Center do not have any date. I know the centers that are not active nowadays. That's why I'm asking for ideas...