Forum Discussion

NBOnecall's avatar
NBOnecall
Icon for Helper V rankHelper V
7 years ago
Solved

Measure to Show Duplicates in a Matrix Visual

Hi,    I have the below data in a matrix and I would like to only show the duplicates.    I have tried to use this formula,   Measure = CALCULATE(counta('ns SettlementItemPrice'[FixedSKU]),ALL...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi NBOnecall ,

     

    Create the following measure:

    CountRows = 
MAXX (
    ADDCOLUMNS (
        SUMMARIZE ( SKU; SKU[FixedSKU]; SKU[Fee]; SKU[Price] );
        "CountRows"; CALCULATE (
            COUNTROWS ( SUMMARIZE ( SKU; SKU[FixedSKU]; SKU[Fee]; SKU[Price] ) );
            ALLEXCEPT ( SKU; SKU[FixedSKU] )
        )
    );
    MAX ( [CountRows] )
)

    Then add it to the filter pane and select all values different from 1.

     

    See attach PBIX.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi NBOnecall ,

 

The use of the ALLEXCEPT formula take out all the filter context from your measure except the columns you place in it, in this case believe that is the problem with your measure because you are placing in the EXCEPTION the FixedSKU column so the context for each row is kept.

 

Don't know the details of your model but you should use an ALL or ALLSELECTED syntax in order to get the count of the two rows.

 

Can you please share some sample data and expected result?

 

Regards,

MFelix

  • NBOnecall's avatar
    NBOnecall
    Icon for Helper V rankHelper V
    7 years ago

    MFelix 

    So please see the first picture, this is the raw data. 

    These would be the visualizations as the table of data is being summed on the quantity with a Measure to count how many lines are in the visualization witht the same SKU. 

    The expected out put would be the final image, after filtering out the rows with 1 count for the measure. 

    Thanks. 

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      7 years ago

      Hi NBOnecall ,

       

      Create the following measure:

      CountRows = 
MAXX (
    ADDCOLUMNS (
        SUMMARIZE ( SKU; SKU[FixedSKU]; SKU[Fee]; SKU[Price] );
        "CountRows"; CALCULATE (
            COUNTROWS ( SUMMARIZE ( SKU; SKU[FixedSKU]; SKU[Fee]; SKU[Price] ) );
            ALLEXCEPT ( SKU; SKU[FixedSKU] )
        )
    );
    MAX ( [CountRows] )
)

      Then add it to the filter pane and select all values different from 1.

       

      See attach PBIX.

       

      Regards,

      MFelix

      • NBOnecall's avatar
        NBOnecall
        Icon for Helper V rankHelper V
        7 years ago

        MFelix 

         

        How would I go about if I want to omit data if it has a $0.00 Fee from the measure?