cancel
Showing results for 
Search instead for 
Did you mean: 
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"-))



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
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"}))

 

 



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.