cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HassanAshas
Helper I
Helper I

How to use Parameter to Filter Last Months Data and Remove Blanks from Table Power BI

I have a sample dataset of Sales. I need to create a Table showing Sales by Customer in different Months and also provide user with an option to show specific number of Last Months. Moreover, if for any of the customers, any month has zero sales (that is Blank value), then that customer should not be shown. 

 

You may download the Power BI file from here for better clear understanding: https://drive.google.com/file/d/1MIZBhRlmdpicmNpPWYuTMSuDdhDKnGMI/view?usp=sharing

 

For example, Consider the following image,

 

HassanAshas_3-1669534550780.png

 

 

1. I want the Parameter on the left to interact with the Table. If value is zero, it should only show "December" Sales, if the value is "1", it should show "June" and "May" Sales. Something like this, 

 

HassanAshas_4-1669535204060.png

I created a Calculated Column called "Month Difference From June 2020" that stores the number of months each date is from "June 2020". I have been trying to use that same column to somehow make the Last Months Filter Parameter to interact with the Table, but I have not been successful in doing so, so far. I think the problem I am facing is I am not able to use SelectedValue function properly in the measure to get the value from Last Months Filter (it returns Blank for some weird reason). 

 

Second thing that I want to do is actually eliminate all the Rows where there exists a single Blank value. 

 

For example, from the following picture, we shouldn't be getting the rows of "Flawless Stores" and "Synthetic" because they have one blank value.

 

HassanAshas_4-1669535204060.png

 

Similarly, if I have all Month Sales (like in first picture), then Logical Stores also shouldn't be shown because it has a blank value for April, (but Logical Stores should appear when last months filter is set to "1", as in the image above) 

 

I understand how I can do this in programming terms, but I am unable to replicate the same in DAX and in Power BI. 

 

The kind of logic I am trying to implement is this, 

 

1. Get the Value of Last Months Filter Parameter and Check if it is Less than the "Months Difference from June 2020" value or not. If it is less, then Return 1, otherwise return 0. 

2. Apply this Measure into the VIsual Filter and only show the values which have "1" (and hence, filtering out all the records where month doesn't come in the last months parameter) 

3. Group the Transactions by the Customer and Month Field. 

4. Calculate the Count for each "Customer" in that grouped Table (so as to calculate the number of months in which their sales exist) 

5. Create a new measure which Returns True if Value of the above calculated count >= Last Months Filter Parameter Value. Otherwise, return false. 

6. Add this Measure in the Visual Filter and only show the rows where measure value is True (so only those Customers will be shown, which are present in all the months that are being filtered) 

 

Unfortunately, I am unable to implement this same thing in Power BI even though I believe my logic is quite correct. Can anyone help me out in this? It will be a huge favor, thank you! 

 

You can download the Power BI File from here: https://drive.google.com/file/d/1MIZBhRlmdpicmNpPWYuTMSuDdhDKnGMI/view?usp=sharing

1 ACCEPTED SOLUTION

Hi @HassanAshas ,

 

You can try this method:

New two Columns in the transactions table:

Month = MONTH('transactions'[order_date])
Slicer = IF('transactions'[Month] = 5 || 'transactions'[Month] = 6, 1, IF('transactions'[Month] = 12, 0, 2))

And set like this:

vyinliwmsft_0-1669618702022.png

vyinliwmsft_1-1669618723354.png

vyinliwmsft_2-1669618734616.png

Then to remove the blank rows, you can new a measure:

RemoveBlank =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        FILTER (
            ALLSELECTED ( 'transactions' ),
            [customer_code] = MAX ( 'customers'[customer_code] )
        )
    )
VAR _total =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        ALLSELECTED ( 'transactions' )
    )
RETURN
    IF ( _count = _total, 1 )

vyinliwmsft_3-1669618833967.png

 

 

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

2 REPLIES 2
HassanAshas
Helper I
Helper I

Anyone who can help on this? Sorry for bumping this up. 

Hi @HassanAshas ,

 

You can try this method:

New two Columns in the transactions table:

Month = MONTH('transactions'[order_date])
Slicer = IF('transactions'[Month] = 5 || 'transactions'[Month] = 6, 1, IF('transactions'[Month] = 12, 0, 2))

And set like this:

vyinliwmsft_0-1669618702022.png

vyinliwmsft_1-1669618723354.png

vyinliwmsft_2-1669618734616.png

Then to remove the blank rows, you can new a measure:

RemoveBlank =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        FILTER (
            ALLSELECTED ( 'transactions' ),
            [customer_code] = MAX ( 'customers'[customer_code] )
        )
    )
VAR _total =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        ALLSELECTED ( 'transactions' )
    )
RETURN
    IF ( _count = _total, 1 )

vyinliwmsft_3-1669618833967.png

 

 

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

 

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.