Forum Replies Created

Viewing 15 posts - 1 through 15 (of 15 total)
  • Author
    Posts

  • dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Thank you Tom.

    in reply to: filtering / DAX aggregation / Countrows #11989

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    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.

     

     

    in reply to: grand total aggregation #11083

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Hi Tom,

    I got sidetracked with another view that took priority and now that I am back on the one that requires this type of aggregation, I just wanted to reach out and say thank you for this. Summarize appears to be a very helpful function for trickier rollups and it worked perfectly.

    Best,

    Dan

    in reply to: Is this type of filtering possible? #11039

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Thank you Tom.

    in reply to: Is this type of filtering possible? #11034

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    I ran into a few snags. I noticed that apparently the natural inner join did not allow the filter, it wasn’t until I added the measure which created the comma separated list that the attribute slicer actually filtered the pivot. The other thing I ran into is my base table has duplicate product id’s due to channel ( direct, retail, etc…) . So , I ended up creating a unique product table and used that to join to my base table and to the product_attribute table. Once I had those two things in place it looks like the filtering worked. I added the following to the $ amt we wanted to the $ Amt measure: $_Amt:=IF (
    HASONEVALUE ( Data[LEVEL_ID] ),
    SUMX (
    FILTER ( Data, [Has At Least One of the Selected Attributes] ),
    Data[CHNL_PMI_AMT_TY]
    ),
    CALCULATE (
    SUMX (
    FILTER ( Data, [Has At Least One of the Selected Attributes] ),
    Data[CHNL_PMI_AMT_TY]
    ),
    FILTER ( Data, Data[LEVEL_ID] = “STYLE” )
    )
    )

    What we are trying to do here is if the user does not pick a level (STYLE,ITEM, PROD) , then the $ Amt is summed at the style level, if the pick one then it is done at that level. I added in that there also needs to be a filter on attribute to the sum. Does anything about this calculation look off to you. It appears to work , but have not done any $ for $ validation yet. However, the filter on the attribute stuff is working great. Thanks for your help.

    Regards,

    Dan

    in reply to: Is this type of filtering possible? #11032

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    never mind. I forgot to relate the tables. apparently with a natural inner join you have to have the relationship created in the datamodel, and what it looks like is the natural inner join function must override the left outer join. It is working now.

     

    in reply to: Is this type of filtering possible? #11031

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    I made a Staging table against my actual dataset on the sheet (connection only), formatted all of the attribute columns and the Product column (called “Product_Memb_ID”) as text.  Referenced this query to create the Product_Attributes table and then referenced the Product_Attributes query to create the Attribute table in the model. And then when I try to add the naturalinnerjoin Has At Least One of the Selected Attributes:=IF ( COUNTROWS ( NATURALINNERJOIN ( Attributes, Product_Attributes ) ) > 0, 1, BLANK() ) it tells me no common join expression detected. Product Attributes: contains columns PRODUCT_MEMB_ID, Attribute Header, and Attribute. Atributes: contains column Attribute. Based upon the tables it looks to me that the natural innerjoin should not be throwing an error. Ever experience this type of error before? And how did you fix it?

    Thanks,

    Dan

    in reply to: Is this type of filtering possible? #11028

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Great. Thank you for your time. Always pressing against what the limits are of Power Pivot here at work.

    in reply to: Is this type of filtering possible? #11026

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Hi Tom,

    This is excellent. Thanks.

    Best,

    Dan

    in reply to: Is this type of filtering possible? #11018

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Hi Tom,

    Yes. I did, but it must have had an issue at upload. Thank you for your reply. I am going to try attaching it again. There is no VBA in it, it just has a sample table the calculation to determine the “Y” or blank and a pivot. Basically, just my thoughts as I am trying to work this through.  Thank you so much for taking a look.

    Best,

    Dan

    Attachments:
    You must be logged in to view attached files.
    in reply to: Is this type of filtering possible? #11016

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    I am having difficulty understanding how to implement concatenatex for this scenario. I think I might be trying to use a hammer here. This is what I was thinking I could do.

    In my test spreadsheet. I created a list of attributes say (flannel, twill, denim, etc..),

    when the user selects one of those attributes, then plan is to use VBA to update the keyword with the value they selected (flannel – for example ) and then refresh the pivot. The column with the DAX ContainsX would execute against the refreshed pivot  and only the rows that have a value matching “flannel” would get a “Y”; therefore, the pivot (filtered to columns with a value of “Y”) would only show me flannel rows. However,  it feels like I might be adding more steps here than are really needed or overcomplicating this a bit. Is there an easier way to do this?

    in reply to: Is this type of filtering possible? #11012

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Thanks Tom. Yes, you are picturing it correctly. Would this Yes, when found, blank when not be something done using something like Robs “ContainsX” function ( https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table-2/ )  where you would need some sort of keyword table? Thanks again.

    in reply to: Power Pivot / Power Query question #11009

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    thanks again for your help Tom.

    in reply to: Power Pivot / Power Query question #11006

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    concatenatex probably would have worked, but this way no DAX is required in the calcs.

    in reply to: Power Pivot / Power Query question #11005

    dfish
    Participant
    • Started: 5
    • Replies: 15
    • Total: 20

    Hi Tom. Thank you. ConcatenateX seemed like the most logical choice. I was doing something wrong in one of the unpivot steps. Once it was corrected it worked – so our solution at a high level and leaving out numerous steps we ended up unpivoting the store ids, getting the store names from a store reference table and then re-pivoting the store names.

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