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.





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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!