True Unduplicated

[TuckRhodes]

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!

[TuckRhodes]

UPDATE - 2/18/2025

Adding source data for my Power BI report

CATEGORY	ITEM	ID
CAT1	SHOES	1
CAT1	SOCKS	2
CAT2	SHIRT	3
CAT2	PANTS	4
CAT1	PANTS	4

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

[Anonymous]

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]

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):

CATEGORY	ITEM	ID
CAT1	SHOES	1
CAT1	SOCKS	2
CAT2	SHIRT	3
CAT2	PANTS	4
CAT1	PANTS	4

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]

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]

Apologies, its been a few days. I was excited, as this seemed promising and it did actually achieve what you told me it would.

However, there is a problem I failed to mention - I need this unduplicated count to dynamically calculate with respect to a date filter. For example, consider the following data:

CATEGORY	ITEM	ID	SELL_DATE
CAT1	SHOES	1	1/1/2025
CAT1	SHOES	1	12/8/2024
CAT1	PANTS	2	11/23/2024
CAT2	SHIRT	3	2/1/2025
CAT2	DRESS_SHIRT	4	11/2/2024
CAT3	SHOES	1	12/27/2024

Consider the following desired outcomes based on date filter scenarios:
Filter 1: 1/1/2025 - 2/24/2025

 - CAT1: 2 (PANTS and SHOES ordered on 1/1/2025)

 - CAT2: 2 (SHIRT and DRESS_SHIRT)

 - CAT3: 0

Filter 2: 10/1/2024 - 1/31/2025

 - CAT1: 2 (the EARLIEST SHOES order and PANTS)

 - CAT2: 1 (DRESS_SHIRT)

 - CAT3: 0 (this order of shoes was not the EARLIEST and therefore is not counted)

Filter 3: 12/27/2024 - 1/31/2025

 - CAT1: 1 (PANTS only bc the SHOES order in CAT1 is not the EARLIEST)

 - CAT2: 0 (no items in this category is in the filtered range)

 - CAT3: 1 (earliest instance of a SHOES order)

So, in summary, I need the solution to do the following:

- first filter the table by the date

- choose the earliest order of each category in the resulting table.

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[TuckRhodes]

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.