Distinct count of values based on date in DAX

Question

I have a table like shown below:

ID	Date	Asset	Location
145	7/29/22	A	Market
145	7/30/22	A	Warehouse
145	7/29/22	B	Market
145	7/29/22	C	Truck
150	7/30/22	B	Market
145	7/29/22	D	Market
145	7/30/22	A	Market

What I am trying to accomplish is to get a distinct count of IDs for each date with a location filter as well. So I would want a count of ID based on the slicer selected Date of 7/29/22 AND 7/30/22 for the Market Location. The desired result is 2 for the selected dates from the date slicer which directly corresponds to the date column in the table.

I was trying to use this DAX formula and wasn't getting anywhere....

IDsMarket =
CALCULATE (
    DISTINCTCOUNT ( 'Products'[ID] ),
    ALL ( 'Products' )
)

I have a measure dropped onto a card. I should have specified that. My apologies. I need 1 measure to show me the combined count for the two days selected.

I tried this with countrows as well but of course the result wasn't distinct... Any help would be greatly appreciated!!

On 7/29/22 there is ID 145 only. What do you mean with "The desired result is 2 for the selected dates"? — Peter
– Peter, Commented Aug 11, 2022 at 19:49
Basically that I want the measure to realize that even though the ID number is the same, to count it twice because the date is different... — ZRaptor
– ZRaptor, Commented Aug 11, 2022 at 20:05

Peter · Accepted Answer · 2022-08-11 20:14:12Z

1

The formula you're looking for is

IDsMarket = 
CALCULATE(
    DISTINCTCOUNT('Products'[ID]),
    'Products'[Location] = "Market"
)

The resulting Table will look like this

But if you put the measure on a Card visual, you'll get

So in DAX the same measure can yield 1000 different values - depending on the filter context.

edited Aug 11, 2022 at 20:14

answered Aug 11, 2022 at 19:37

Peter

12.7k2 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ZRaptor Over a year ago

That works in a table, but I have a measure dropped onto a card. I should have specified that. My apologies. I need 1 measure to show me the combined count for the two days selected.

Peter Over a year ago

What do you mean with combined count?

ZRaptor Over a year ago

Because the user has the two dates selected in a slicer on the dashboard, I need the measure to add those counts together to be 3. If the user selects a third date in the slicer, then the measure would add the count from that day as well and so on.

Peter Over a year ago

So now it's 3? For how long will you be changing your question?

ZRaptor Over a year ago

You are right, I am sorry, my mind is all over the place trying to figure this out. adding and deleting code constantly. I've looked at too much for a day. When I enter that exact code into PBI, it just returns a value of 1. Not 2

ZRaptor · Accepted Answer · 2022-08-11 21:44:51Z

1

I created a conditional column in Power Query and combined the ID with the "day" number from the date column which allowed me to then do a distinct count on that combined custom column which produced to correct answer. Sorry for all the confusion. One of those days.

answered Aug 11, 2022 at 21:44

ZRaptor

1073 silver badges18 bronze badges

1 Comment

Mik Over a year ago

if Peter was correct answering your question, then you can upvote him at least, and if you messed with the question you can accept his answer.

Collectives™ on Stack Overflow

Distinct count of values based on date in DAX

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related