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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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"}))

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.