Distinct count not giving the corrent number

[chiru5262]

Hi below is my measure

Inspection Repair Count_AssetDesc_Asset =

Calculate(COUNT(FACT_WORKORDER[Assetnum]),Filter(DIM_ASSET,SEARCH("valve",DIM_ASSET[ASSET_DESC],1,0)>0), Filter(DIM_ASSET,DIM_ASSET[Asset Class Type] in {"Linear Asset","Network Facility"}), FILTER(

        FACT_WORKORDER,

        FACT_WORKORDER[Work Type] IN {"BM","CM"}

        && NOT(FACT_WORKORDER[Status] IN {"CAN","CANDUP","DRAFT"})))

The out put for 2023-24  fin year is 4145, but when I use slicer on work type BM and CM its 2020 and 2425 which doesnt add up 4145

Not sure where I went wrong

Appreciate any help

[chiru5262]

@amitchandak  Hi Mate could you please advise on the above, should be an easy fix for you, not sure what I'm missing

[chiru5262]

@lbendlin  HI Mate could you please advise on the above, I have seen your previous you solved similar issues easily

[ahadkarimi]

Inspection Repair Count_AssetDesc_Asset =
CALCULATE(COUNT(FACT_WORKORDER[Assetnum]),FILTER(FACT_WORKORDER, FACT_WORKORDER[Work Type] IN {"BM", "CM"} && NOT(FACT_WORKORDER[Status] IN {"CAN", "CANDUP", "DRAFT"}) ), FILTER(
DIM_ASSET,
SEARCH("valve", DIM_ASSET[ASSET_DESC], 1, 0) > 0 &&
DIM_ASSET[Asset Class Type] IN {"Linear Asset", "Network Facility"}))

[Greg_Deckler]

@chiru5262 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[sroy_16]

Hello,

Please change the measure to this and see if it works or not,

Inspection Repair Count_AssetDesc_Asset =
CALCULATE(
COUNT(FACT_WORKORDER[Assetnum]),
FILTER(
ALL(DIM_ASSET),
SEARCH("valve", DIM_ASSET[ASSET_DESC], 1, 0) > 0
&& DIM_ASSET[Asset Class Type] IN {"Linear Asset", "Network Facility"}
),
FILTER(
FACT_WORKORDER,
FACT_WORKORDER[Work Type] IN {"BM", "CM"}
&& NOT(FACT_WORKORDER[Status] IN {"CAN", "CANDUP", "DRAFT"})
)
)

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

[chiru5262]

But I need distinch count becasue in the workorder table I have multiple wonum related to one assetnum

[jdbuchanan71]

It sounds like you have some items that are in both categories so when you count both the item only gets counted once but they also get counted when you look at just BM and just CM.