cancel
Showing results for
Did you mean:
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
Super User

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

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

3 REPLIES 3
Super User

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

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

Helper I

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!

Helper I

### Amazing, thanks!

Announcements

#### 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 Design Challenge

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

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors