cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodfernandez
Helper I
Helper I

How to use Filter selection as a VAR in DAX

Hello,
I have the following table

Id

Month

Zone

condition

1

1

South

Open

2

1

North

Open

3

1

North

Close

4

2

South

Open

5

2

North

Close

6

3

North

Close

7

3

South

Close

8

3

North

Open

9

4

North

Close

10

5

North

Open

11

5

South

Close

12

6

South

Close

13

7

North

Close

14

7

South

Open

15

8

North

Open

16

9

South

Close

17

10

North

Close

18

11

North

Close

19

11

North

Open

20

12

South

Close

 

 

 

 

 

I need to create a table that in the first row count the amount of “Id” for any condition (Open or Close) from month 1 until the selected month in the filter for each zone.

In the second row, I need to show the same number calculated in the top row but multiply by the select month number and divided by 12

In the third row, I need to count the amount of “Id” but only the ones that are “Open” from month 1 until the selected month in the filter for each zone.

For example if you choose month 4 in the filter, you should get the following table

 

South

North

Total

Number of events

3

6

9

Percentage at this month

1

2

3

Number of open events

2

2

4


Were the 1, 2 and 3 in the second row comes from this formula, for the first column is 3*4(select month)/12 and for the second column is 6*4(select month)/12

thanks

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@rodfernandez solution is attached, you can change it as per your need.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@rodfernandez solution is attached, you can change it as per your need.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

One little question, what would have change in this measure 

Number of Open Events = CALCULATE( [Number of Events]; Table1[condition] = "Open" )

if in the condition column instead of  having 2 possible states ("Open" and "Close") you would have 4 possible states ("Open", "Close", "In Maintenance" and “ReOpen” ) and you want to display all the ones that are "Open" and "In Maintenance".

Thanks!

 

Amazing, thanks! 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors