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

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_nouser_idcampaign_iddate
1111null1/1/2021
21111012/1/2021
3111null3/1/2021
41111024/1/2021
51111035/1/2021
6111null6/1/2021
7111null7/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_nouser_idcampaign_iddate
21111012/1/2021
3111null3/1/2021
41111024/1/2021
51111035/1/2021
6111null6/1/2021
7111null7/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 @V-pazhen-msft 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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. 

Vpazhenmsft_0-1636594933388.png

Measure =
var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))
Return IF(MAX([date])>=SelectedID_Date,"true")
Vpazhenmsft_1-1636595270006.png


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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. 

Vpazhenmsft_0-1636594933388.png

Measure =
var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))
Return IF(MAX([date])>=SelectedID_Date,"true")
Vpazhenmsft_1-1636595270006.png


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Thank you so much @V-pazhen-msft 

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?

 

monk_0-1636699132309.png

 

 

 

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")

 

 

 

 

Anonymous
Not applicable

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"))

 

 

Anonymous
Not applicable

FYI - if the source is Direct Query and the data size is huge, it will throw a "resultset exceeds 1M rows" error. This logic tries to bring the complete data to the analysis service first before applying DAX. 

 

A workaround is to keep this in a custom SQL query while connecting to the data source. 

 

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