Forum Discussion
selecting all rows after a minimum value within a group.
Hi fellow Power BI developers,
Need help in creating DAX - calculated table. Please let me know if the below description is not clear.
I have data in the below format.
| row_no | user_id | campaign_id | date |
| 1 | 111 | null | 1/1/2021 |
| 2 | 111 | 101 | 2/1/2021 |
| 3 | 111 | null | 3/1/2021 |
| 4 | 111 | 102 | 4/1/2021 |
| 5 | 111 | 103 | 5/1/2021 |
| 6 | 111 | null | 6/1/2021 |
| 7 | 111 | null | 7/1/2021 |
Q: When campaign_id is selected in filter pane ex: 101, I would like to get all the rows from row_no 2 to 7 for user ID 111.
Expected output:
| row_no | user_id | campaign_id | date |
| 2 | 111 | 101 | 2/1/2021 |
| 3 | 111 | null | 3/1/2021 |
| 4 | 111 | 102 | 4/1/2021 |
| 5 | 111 | 103 | 5/1/2021 |
| 6 | 111 | null | 6/1/2021 |
| 7 | 111 | null | 7/1/2021 |
Here is the working SQL solution:
Select main_table.user_id, main_table.campaign_id, main_table.date
From test_table as main_table
Inner join
(Select test_table.user_id, min(test_table.date) as min_date
From test_table
Where campign_id in (values selected in filter)
group by test_table.user_id) as grouped_table
on main_table.ID = grouped_table.user_id
where main_table.date >= grouped_table.min_date
Group by main_table.user_id, main_table.campaign_id, main_table.date
Query explanation: Group by user_id and get min(date) row within the selected campaign_id (from filter) rows. For each user ID, select all rows after min(date) [irrespective of campaign_id].
Other info:
Source: Direct Query (snowflake)
The above calculated table should be generated every time a date is selected in the slicer, new campaign Id is selected in the right filter pane.
Planning to use the above calculated table to create new measures like count rows, distinct rows, sum, etc.,
Please let me know if you need more info. I'm open to trying a different approach to get the same results.
v-robertq-msft Anonymous
Anonymous
The sample pbix is import mode otherwise you cannot access the data, but the steps should also work with Direct Query.
Since you are using direct query, you cannot create calculate table, instead you can add a distinct in power query. This table is used as slicer to filter your original table when comparing using dax.Measure =var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))Return IF(MAX([date])>=SelectedID_Date,"true")
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous
The sample pbix is import mode otherwise you cannot access the data, but the steps should also work with Direct Query.
Since you are using direct query, you cannot create calculate table, instead you can add a distinct in power query. This table is used as slicer to filter your original table when comparing using dax.Measure =var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))Return IF(MAX([date])>=SelectedID_Date,"true")
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Thank you so much Anonymous
I tried your solution by modifying the measure a bit to get results for group by user_id.
Unfortunately, your solution returns true for rows which has no association with the selected campaign_id at all.
Please see the screenshot where user_id 333 is associated with only campaign Id 0 (null). Still the measure is returning true / 1 for campaign ID 102 selected from drop down.
I have created one more measure (count_measure) to get the count(unique user_id). It should be 2. Instead I see 3 because it is considering user_id 333 also.
is there a way we can get only user 111 and 222 as output and unqiue user_id count as 2?
Measure = var selected_campaigns = VALUES('Table'[campaign_id]) var SelectedID_Date = CALCULATE( MIN([date]), FILTER( ALL(Sheet1), --'Sheet1'[campaign_id]=SELECTEDVALUE('Table'[campaign_id]) Sheet1[campaign_id] IN selected_campaigns && Sheet1[user_id] = SELECTEDVALUE(Sheet1[user_id]))) Return IF((MIN(Sheet1[date])>=SelectedID_Date),"TRUE", "FALSE")I figured out how to get only user_ids which are relevant to selected campaign_id
added user_ids to unique campaign_id table.
Measure = var selected_campaigns = VALUES('Table'[campaign_id]) var selected_users = VALUES('Table'[user_id]) var SelectedID_Date = CALCULATE( MIN([date]), FILTER( ALL(Sheet1), --'Sheet1'[campaign_id]=SELECTEDVALUE('Table'[campaign_id]) Sheet1[campaign_id] IN selected_campaigns && Sheet1[user_id] = SELECTEDVALUE(Sheet1[user_id]))) Return IF((MIN(Sheet1[date])>=SelectedID_Date) && (min(Sheet1[user_id]) IN selected_users),"TRUE", "FALSE")unique_user_count = CALCULATE(DISTINCTCOUNT(Sheet1[user_id]), FILTER(Sheet1, Sheet1[Measure] = "TRUE"))