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
Anonymous
Not applicable

DAX formulas for subfiltering a column based on another column

Let's see if I can make this message clear Smiley Wink

I have a set of data, that contains User IDs and their purchasing date. I want to know how many distinct users were active (had transactions) during a month. This is easy as I just use DISTINCTCOUNT(), and get the desired result. The problem arises, when I want to filter out said users. I want to select a month to the count of the distinct users during that month AND how many of them were active during the whole observation period. For example , i want to select people that were making a purchase during the January of 2018, and then see how many of said people were active during other months. The problem is, that I need to do this using DAX functions, not querry or external data manipulation tools. ive tryed several different things, but can not seem to come to an answer. I am not a very experienced power BI user, but this is one of my latest attempts (though unsuccsesful): 

 

Activity_users =
VAR User = DISTINCT('Papildomi pavyzdiniai duomenys Andriui'[UserId])
VAR test = "2014-01"
VAR active = IF(VALUES('Papildomi pavyzdiniai duomenys Andriui'[TransActionYM]) = test, 1 , BLANK())
VAR Grouped = SUMMARIZE('Papildomi pavyzdiniai duomenys Andriui','Papildomi pavyzdiniai duomenys Andriui'[UserId],"useriai", active)
--VAR ifactive = IF(active=1, DISTINCT('Papildomi pavyzdiniai duomenys Andriui'[UserId]),BLANK())
--VAR final = CALCULATE(DISTINCTCOUNT('Papildomi pavyzdiniai duomenys Andriui'[UserId]),active>0)
RETURN
COUNTX(Grouped,active)
 



commented lines are other attempts to this problem.

1 ACCEPTED SOLUTION

@Anonymous here is what you can do:

 

- create another table for slicer, which you can use folloing DAX expression

 

Date = VALUES( Table2[Transaction_year_month] )

- add a slicer and Transaction_Year_Month column from this new Date Table

 

Add following measure in your user table

 

Users = 
VAR usersinSelectedMonth = 
CALCULATETABLE( 
    VALUES( Table2[User] ), 
    Table2[Transaction_year_month] IN VALUES( 'Date'[Transaction_year_month] ) 
)
RETURN
CALCULATE( 
    DISTINCTCOUNT( Table2[User] ), 
    usersinSelectedMonth 
)

Create a bar chart, use Transaction_Per_month and Users measure from your user table and you will have the result.



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

6 REPLIES 6
parry2k
Super User
Super User

@Anonymous if you can share sample data in excel with exepcted output, will get you the solution



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.

Anonymous
Not applicable

Transaction_year_monthUser
2011-01Bob
2011-02Bob
2011-03Bob
2011-04Bob
2011-05Bob
2011-06Bob
2011-07Bob
2011-08Bob
2011-09Bob
2011-10Bob
2011-11Bob
2011-12Bob
2011-02Adam
2011-03Adam
2011-04Adam
2011-06Joe
2011-07Joe
2011-08Joe
2011-01Victor
2011-05Victor
2011-09Victor
2011-01Peter
2011-02Peter
2011-01Carl
2011-02Carl
2011-03Carl
2011-04Carl
2011-05Carl
2011-06Carl
2011-07Carl
2011-08Carl
2011-05Stewart
2011-06Stewart
2011-07Stewart
2011-08Stewart
2011-09Stewart
2011-10Stewart
2011-11Stewart
2011-12 

 Thank you for your response, I can not share the actual data, but I am including some simplified mock data. The idea would be, that if I selected one of the dates, let us say 2011-05, i need to see all people that had transactions that day as well as what other months they were active. The graph should includea spike on selected month and some data points trhoughoutthe whole period. My actual data is agregated to days rather then months as well. Than kyou very much for your help!

@Anonymous is this the result you are expecting if May 2011 is selected

 

image.png



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.

Anonymous
Not applicable

Yes! looking at the data and the intended visual, this is how it should look!  though not sure if it is possible to selecet more than one month using a slicer to have two line graphs for two seperate months? what exact DAX formula did you use to come to this result? and thank you very much for your help!

@Anonymous here is what you can do:

 

- create another table for slicer, which you can use folloing DAX expression

 

Date = VALUES( Table2[Transaction_year_month] )

- add a slicer and Transaction_Year_Month column from this new Date Table

 

Add following measure in your user table

 

Users = 
VAR usersinSelectedMonth = 
CALCULATETABLE( 
    VALUES( Table2[User] ), 
    Table2[Transaction_year_month] IN VALUES( 'Date'[Transaction_year_month] ) 
)
RETURN
CALCULATE( 
    DISTINCTCOUNT( Table2[User] ), 
    usersinSelectedMonth 
)

Create a bar chart, use Transaction_Per_month and Users measure from your user table and you will have the result.



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.

Anonymous
Not applicable

Thank you very much, works like a charm!

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.

Top Solution Authors