Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.