Forum Discussion

TuckRhodes's avatar
TuckRhodes
Helper I
1 year ago

True Unduplicated

I am attempting to count unique items. I would also like to break them down in a clustered bar chart. My approach has been this:

 

1. Create a measure called Unique Items = 

DISTINCTCOUNT('Table'[ID])
2. Have a card showing the total unique item count
3. Have a clustered bar chart that breaks this total down by category.
 
In terms of the card requirement, this does the job. The discrepancy comes in when I use the measure as x-axis values and categories as y-axis values for the clustered bar chart.
 
The bar chart appears to be deduplicating within the context of the categories. This causes the sum of the data lables of the bars to be different from the value on the car (which is what I want it to sum to). In the following screenshot, I have purposefully made an item that appeared in Cat1 and Cat2. It is counted twice in the bar chart total but not in the card. The end goal is to have both cat 1 and 2 have 2 items each totaling to 4 and matching the card at the bottom. I am trying to avoid making a copy of the underlying table that has unique values as the actual data set I am using is quite large and would cause a significant performance hit. 
 
Is there any way I can modify my measure to achieve this? Or take some other approach? All suggestions are welcome!
 
Thanks in advance!
Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • TuckRhodes's avatar
      TuckRhodes
      Helper I
      1 year ago

      Hey thanks for the links! I actually was wondering how to do some of that. I have attached the source data in a table in a reply to the original post. I also attached the screenshot of the power query as the table looked strange with no vertical separators in the preview.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TuckRhodes ,

    I'm sorry but I can't understand your logic. Here is the sample data I guessed you created based on your description:

    The end goal is to have both cat 1 and 2 have 2 items each totaling to 4 and matching the card at the bottom.

    I guess the effect you want to achieve is like this:

     

    But I want to know why it is CAT1=2, CAT2=2, 2+2=4? Why can't it be CAT1=3, CAT2=1, 3+1=4? From which Category should the duplicate item be deleted, and what is the logic of this selection?

    Best Regards,
    Dino Tao

    • TuckRhodes's avatar
      TuckRhodes
      Helper I
      1 year ago

      Thanks for the reply, Dino. I have attached the source data in the reply to the original post.

       

      Allow me to ellaborate a bit more - below is the source data I attached in my reply to the OP (sorry if it looks strange - it keeps autocorrecting due to some weird HTML error):

      CATEGORYITEMID
      CAT1SHOES1
      CAT1SOCKS2
      CAT2SHIRT3
      CAT2PANTS4
      CAT1PANTS4

       

      I basically want a "first listed" count in that the bottom row would not be counted for CAT1 because the first time this item is encountered it is in CAT2. This is why the desired result in the bar chart is CAT1 = 2 and CAT2 = 2 and not CAT1 = 3 and CAT2 = 1.

       

      Is that clearer? Do you have any follow up questions?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi TuckRhodes ,

        Thank you for the detailed explanation!
        You need to have an Index column to identify the first time the ID appears, like this:

        Considering you mentioned that your data is huge, I would recommend doing this in Power Query to prevent possible performance and memory issues:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYMMVTSUQr28HcNBtKGSrE6CEF/Z2+QoBFM0Ais0jMoBEgbIwsGOPqFgFSaIGtHEowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CATEGORY = _t, ITEM = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CATEGORY", type text}, {"ITEM", type text}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"CATEGORY", "ITEM", "Index"}, {"CATEGORY", "ITEM", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Index", Int64.Type}})
in
    #"Changed Type1"

         

        Put all of the M code into the Advanced Editor:

        And click "Close & Apply":

        Then use this DAX to create a measure:

         

        Count_Chart = 
CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    'Table'[Index] = 1
)

         

        And the final output is as below:


        Best Regards,
        Dino Tao
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • TuckRhodes's avatar
    TuckRhodes
    Helper I
    1 year ago

    UPDATE - 2/18/2025

    Adding source data for my Power BI report

    CATEGORYITEMID
    CAT1SHOES1
    CAT1SOCKS2
    CAT2SHIRT3
    CAT2PANTS4
    CAT1PANTS4

     

    I'm also attaching a screenshot of the data since the preview is showing the above table looking odd.