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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HassanAshas
Helper V
Helper V

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 V
Helper V

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors