Forum Discussion
Helper IIsumx and allselected
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
I can see that iteration on allselected creates a shadow context
I use ALLSELECTED inside SUMX
so how can I handle my problem
I need the unique count of person whose tickets are open
which means I need to consider only 1 ticket for person when I have 2 open tickets
ticket which needs to be considered as latest open ticket (max of open_dt)
this is my dax for finding the unique count
which results in total in table as correct but the values are still the same as my open ticket count measure
attaching my example file
https://drive.google.com/file/d/1G_sfyGqbAyCbxA359lShcC6z06zDWYIA/view?usp=share_link
11 Replies
andhiii079845Solution Sage
Thank you for directly sharing the PBI. This make it so easy for helping!
Do not forget to mark my solution, thank you 🙂
Try this
Measure 2 =MAXX(FILTER('Table','Table'[close_dt]=BLANK()),'Table'[open_dt])Measure 3 =COUNTX(FILTER('Table','Table'[close_dt]=BLANK()),'Table'[open_dt])
newbie_powerbisure, you have incorporated the logic of open inside the measure but my logic has mutiple conditions for open & my table has 10 millions rows
I just added a simple logic for the understanding
I need the same results using open ticket count measure
thank you
- andhiii079845
Than change it like this:
Measure 2 =MAXX(FILTER('Table','Table'[open ticket count]=1),'Table'[open_dt])
- andhiii079845
Your open_dt and close_dt columns are the reason for the current matrix. can you please share exactly how the matrix should look like?
- newbie_powerbi
thanks for helpin me out andhiii079845
I don't know how it is affecting but
I have attached the link for the sample file
I need to show the table for distinct count of person whose tickets are open
i.e. only 1 row should be considered for a person (if 2 open tickets are there for same person then max open_dt row should be considered)
I should show only the highlighted rows & my count should be 2
https://drive.google.com/file/d/1G_sfyGqbAyCbxA359lShcC6z06zDWYIA/view?usp=share_link
- newbie_powerbi
out of these 9 rows I only need to see the highlighted 2 rows
i.e. distinct count of a person who has latest open ticket