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!

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.

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