cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NBOnecall Regular Visitor
Regular Visitor

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]),ALLEXCEPT('ns SettlementItemPrice','ns SettlementItemPrice'[FixedSKU])),
 
 

but I feel like I am missing a peice as my results are not what I was expecting. 

 

The Image below is my results with that measure. I want the measure to output 2, for the two rows that the FixedSKU shows up in the visualization. 

Matrix Only show duplicates.png

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure to Show Duplicates in a Matrix Visual

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Super User
Super User

Re: Measure to Show Duplicates in a Matrix Visual

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




NBOnecall Regular Visitor
Regular Visitor

Re: Measure to Show Duplicates in a Matrix Visual

@MFelix 

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

Table of Data.PNG

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. 

Expected Output.PNG

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

With filter to remove Count of 1.PNG

Thanks. 

Super User
Super User

Re: Measure to Show Duplicates in a Matrix Visual

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




NBOnecall Regular Visitor
Regular Visitor

Re: Measure to Show Duplicates in a Matrix Visual

@MFelix 

 

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