Home Forums Power Pivot filtering / DAX aggregation / Countrows

This topic contains 1 reply, has 1 voice, and was last updated by  dfish 5 months, 2 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #11986

    dfish
    Participant
    • Started: 4
    • Replies: 14
    • Total: 18

    I have the following calculations in my model. The first one gives me the count of records in the IFA Inspections DB table where the fiscal year, factory and vendor filters remain applied. The second one is a total of records at the fiscal year level. I have two pivot tables. These pivots seem to filter fine, when I add fisc yr as a page filter and filter each pivot individually. However, I have a second table called IFA Fiscal Year joined to IFA Inspections DB. When I add an IFA Fiscal Year.FISC_YR slicer and connect it to the pivot the pivot table doesn’t filter when I pick a year in the slicer unless I remove the calculations below. Is there another way to do these calculations so that the slicer will in fact limit the pivot rows to the selected year?

    IFAInspectionCount:=Calculate(countrows(‘IFA Inspections DB’),ALLEXCEPT( ‘IFA Inspections DB’,’IFA Inspections DB'[Fiscal Year],’IFA Inspections DB'[Factory Name],’IFA Inspections DB'[Vendor Name]))

    TotIFAInspectionCount:=Calculate(countrows(‘IFA Inspections DB’), ALLEXCEPT(‘IFA Inspections DB’,’IFA Inspections DB'[Fiscal Year]))

    I am going to have numerous other pivots and charts that feed off that same year slicer so I am trying to avoid the user having to filter every single pivot and chart to the year they want. I have attached a mock up of the issue. I am wondering if I could give each row a number of 1 and use sumx or summarize, something that would give me the total vendor, factory records for the fisc year selected.

    Attachments:
    You must be logged in to view attached files.
    #11989

    dfish
    Participant
    • Started: 4
    • Replies: 14
    • Total: 18

    One additional mention there are other calcs impacted too

    I do a distinct count of unique vendors

    <span style=”font-size: medium;”>IFADistinctVendors:=COUNTROWS(DISTINCT(‘IFA inspections DB'[Vendor Name]))

    TotIFADistinctVendors:=CALCULATE(IFADistinctVendors],ALLEXCEPT(‘IFA inspections DB’,’IFA inspections DB'[Fiscal Year]))

    The second one messes up the filter.

    TotIFAInspectedUnits:=CALCULATE([IFA Inspected Units],ALLEXCEPT(‘IFA inspections DB’,’IFA inspections DB'[Fiscal Year]))

    TotIFARejectedUnits:=CALCULATE(IFA Rejected Units], ALLEXCEPT(‘IFA inspections DB’,’IFA inspections DB'[Fiscal Year]))

    <span style=”font-size: medium;”>TotIFAInspUnits:=CALCULATE([IFA Inspected Units],ALLEXCEPT(‘IFA inspections DB’,’IFA inspections DB'[Fiscal Year],’IFA inspections DB'[Factory Name],’IFA inspections DB'[Vendor Name]))

    Basically the issue seems to happen when an all except is used to get a total at the fisc year level.

    I was able to get summarize to work for the first calculation in the first post by indexing the table and giving each record a RowCnt of

    IFAInspectionCount:=
    sumx(
    summarize(‘IFA inspections DB’, [Vendor Name], [Factory Name],[Fiscal Year],
    [Index],[RowCnt]),[RowCnt])

    However, I am still struggling to find an alternative to ALLEXCEPT to do the aggregation so the pivot rows in multiple pivots ( different tables related by the fisc yr table ) will filter based upon the fisc yr selected in the fisc yr table slicer and all of my total calculations will still work. If I remove the calcs the pivots filter fine, so I know its the calcs. I just haven’t figured out an alternative way to do the total calcs.

     

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.