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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filtering several fields from the same column into a table using DAX

Hi, i am trying to retrieve 2 different fields within the same column from my data table to populate onto my PowerBI dashboard. For example, my data table contains lots of data, but i will only want to specifically extract data relating to the months of January and February under the dates column of my table.

 

I will like to know if there is a DAX formula that is able to do that? 

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @Anonymous ,

If you want to get date from Jan-March, I think you could try below measure based on my sample.

Measure 2 = CALCULATE(SUM(T3[Revenue]), FILTER(T3,T3[Type]="Service" && T3[mdate]<=MIN('slicer'[month])))

 If this doesn't work, could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
dax
Community Support
Community Support

Hi @Anonymous , 

You said that you want to get the data between date, did you want to show then in the same rows or just show rows which between date slicer? If you want to get rows which between date slicer, you could create a calendar table and create relationship between calendar table and your table, use calendar table as slicer, then it  should work. If this is not what you want, please correct and inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi, let me show an example which hopefully better illustrates my question.

example.PNG

I currently have a date slicer on my PowerBI dashboard in the same format as the dates column in the table shown above. 

The outcome i want to achieve is to create a measure which derives the cumulative revenue for a specific type of revenue when the dates slicer is selected. (I.e. Selecting Feb 20 on the dates slicer filters "Service" type revenues and "Jan-20" and "Feb-20" of revenue data)

example 2.PNG

dax
Community Support
Community Support

Hi @Anonymous , 

You could create a calculated column like below in your table mdate = "01-"&T3[Date], then change its type to date, and create slicer table like below, try to use below measure to see whether it work or not.

Measure = CALCULATE(SUM(T3[Revenue]), FILTER(T3,T3[Type]="Service" && T3[mdate]<=MIN('slicer'[month]) && DATEADD(T3[mdate],1,MONTH)>=MIN(slicer[month])))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi Zoe, thanks for your responses! 

Looking at your table and measure, is there a way to tweak the measure in a way which gives me the revenue values for cumulative months starting January as the date on the slicer changes? I.e. selecting the month of March gives me revenue from January to March

 

dax
Community Support
Community Support

Hi @Anonymous ,

If you want to get date from Jan-March, I think you could try below measure based on my sample.

Measure 2 = CALCULATE(SUM(T3[Revenue]), FILTER(T3,T3[Type]="Service" && T3[mdate]<=MIN('slicer'[month])))

 If this doesn't work, could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@thamzh , Not very clear

There are many ways. You can have one month in data segmentation and filtering. You can have the formula


calculate(sum(table[Column]),format(table[date],"MMM-YYYY") en "Jan-2020","Feb-2020"-)

O
calculate(sum(table[Column]),filter(table, format(table[date],"MMM-YYYY") ? "Jan-2020" ? format(table[date],"MMM-YYYY") ?" Feb-2020"-))

amitchandak
Super User
Super User

@Anonymous , Not very clear

There many ways. You can have month year in slicer and filter. You can have the formula


calculate(sum(table[Column]),format(table[date],"MMM-YYYY") in {"Jan-2020","Feb-2020"})

Or
calculate(sum(table[Column]),filter(table, format(table[date],"MMM-YYYY") = "Jan-2020" || format(table[date],"MMM-YYYY") ="Feb-2020"}))

 

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.