• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Blanks in Power Pivot table

aggie81

Member
I use Power Query to transform data from Excel files into appends. There are two results that are loaded into the data model and a relationship is created between them. The data model is then loaded to a Power Pivot table.
It works well enough but on the Description field on the pivot table, there are blanks that must be filtered out by unchecking them.
The AppOrders are units to be produce for May 9, 2022, in this instance.
The SpOrders2022 has customer orders.
In my mind, the pivot table should only pull in the SpOrders2022 items that match in AppOrders but there are blanks. It may be that the blanks are orphans in the SpOrders2022 with no match in AppOrders table and filter is the easiest way to not show them????
I have attached a workbook and hope it helps.
I manage a production greenhouse and use this to know and tell production crew what orders to target when a planting list is generated.
Thank you for reading and look forward to your thoughts.
Lee
 

Attachments

  • 22. May 9, 2022.xlsx
    681.8 KB · Views: 5
It would help if you upload workbook with pivot table or at least of what your current pivot fields looks like.

Also, make sure to have your data model populated. When I took a look at your workbook. It's missing the model.

As for your question. There are few ways to handle it.
1. Using DAX measure to control what is returned in pivot to remove blanks. Ex: IF(HASONEVALUE(),Calc.Alternate Calc) etc.
2. Using table variable to construct special context for DAX measure.
3. If using PowerBI, you can use calculated table (you can't use it in Excel's data model).

etc.
 
I apologize I grabbed the wrong file to upload. The Pivot Table is on Orderstoplant tab. It should be populated.
Again, I'm sorry.
Thank you.
 

Attachments

  • 22. May 9, 2022 orders.xlsx
    532.2 KB · Views: 5
Replace your 2 value field columns with following DAX measures in your case.

Code:
UnitBooked :=
IF (
    DISTINCTCOUNT ( AppOrders[Crop #] ) = 1,
    SUM ( SPOrders2022[Units Booked] ),
    BLANK ()
)

Code:
ProductionQTY :=
IF (
    DISTINCTCOUNT ( AppOrders[Crop #] ) = 1,
    SUM ( SPOrders2022[Production QTY] ),
    BLANK ()
)
 
Oh, by the way. Above is inflexible method and if you change pivot structure, it's going to return unexpected result.
 
If you need it to be more dynamic and also calculate Grand Total. You need to pass filter context using CALCULATE.
Ex:
Code:
UnitBooked :=
VAR distCount =
    CALCULATE ( DISTINCTCOUNT ( AppOrders[Crop #] ), SPOrders2022 )
RETURN
    IF (
        distCount >= 1,
        CALCULATE ( SUM ( SPOrders2022[Units Booked] ), AppOrders ),
        BLANK ()
    )
 
Thanks for adding the totals, I was in the process of asking about it when I saw your message.
I halfway understand the code you wrote but not fully. It is working well.
I will create a new file next week and hope there aren't any problems.
 
Thank you for sharing the link. Leila Gharani is able to take something complicated and explain it in simple terms and examples.
All is working well.
 
I have problems with my file that you wrote the measures for 2 years ago. I get the following error message but cant solve the problem. Are you willing and able to help.
Thanks,



1706912653692.png
1706912653692.png
 

Attachments

  • 9. January 22, 2024 orders.xlsx
    514 KB · Views: 1
That message has nothing to do with the measure. It's issue between your PowerQuery and loading it to Data model. Check your query dependency.
 
Back
Top