topic Filter and Replace values in DAX Commands and Tips

Filter and Replace values

SCallanan — Thu, 01 Aug 2024 12:10:39 GMT

Hi guys,

I want to filter and replace the SUM of the Distinctcount values when Admin & physical both apply together and replace the total which would be 2 with 1.

Please note, this is only an extract of the dataset. I already did the DistinctCount before with this measure:

DistinctCountInspectionCategory = DISTINCTCOUNTNOBLANK('Inspections'[Inspection Group])

Sorry I couldn't paste all the values, but the relavent two ID's are included.

Thanks for your help.

Trip Inspection Id	Inspection Group	DistinctCountInspectionCategory
9090469	Administrative	1
600384	Administrative	1
600480	Administrative	1
600575	Administrative	1
600671	Administrative	1
600767	Administrative	1
793963	Administrative	1
793997	Administrative	1
794182	Administrative	1
794267	Administrative	1
826837	Administrative	1
829543	Administrative	1
829543	Physical	1
829588	Administrative	1
829588	Physical	1
928621	Administrative	1
928648	Administrative	1
928675	Physical	1
953030	Administrative	1
953104	Physical	1

Re: Filter and Replace values

Gabry — Thu, 01 Aug 2024 12:24:13 GMT

Hello,

SCallanan I'm not sure what you need to do, you just need to distinctcount the trip ID?

If so, is as simple as this:

If not please explain better what you need

Re: Filter and Replace values

SCallanan — Thu, 01 Aug 2024 13:14:28 GMT

Hi Gabry

It is really part of a bigger problem /Count-of-ID-with-filters-based-on-a-different-column-Values

maybe it helps to understand the context if you have a look at that.

Thanks so much, I'm trying to get this right for ages.

Re: Filter and Replace values

Anonymous — Fri, 02 Aug 2024 06:15:27 GMT

Hi, SCallanan

I am glad to help you.

But I am not sure if I have understood your needs correctly.

Perhaps you could start by trying to create a calculated column that marks each Trip Inspection Id as having both "Administrative" and "Physical":

HasBoth = VAR CurrentTripId = Inspections[Trip Inspection Id] RETURN IF ( CALCULATE ( COUNTROWS ( Inspections ), Inspections[Trip Inspection Id] = CurrentTripId, Inspections[Inspection Group] = "Administrative" ) > 0 && CALCULATE ( COUNTROWS ( Inspections ), Inspections[Trip Inspection Id] = CurrentTripId, Inspections[Inspection Group] = "Physical" ) > 0, 1, 0 )

Then create a Measure:

TripInspectionIdCount = CALCULATE ( DISTINCTCOUNT ( Inspections[Trip Inspection Id] ), Inspections[HasBoth] = 1 ) + CALCULATE ( DISTINCTCOUNT ( Inspections[Trip Inspection Id] ), Inspections[HasBoth] = 0 )

Here is the final test result, hope it helps:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.