Forum Discussion
Conditional Distinct Count based on slicers
I have created the following measure for distinct count of case numbers
Case Count =
CALCULATE(
DISTINCTCOUNTNOBLANK(
Fact_Table[Case No.]
),
FILTER(
Fact_Table,
SUM(Fact_Table[Revenue]) > 0 &&
Fact_Table[Category] = "New" &&
Fact_Table[Advisor Name] <> "Direct" &&
Fact_Table[Advisor Name] <> BLANK()
)
)
It is a dataset with thousands of rows. There are multiple dimension tables which connect to the fact table which are used to slice the data by location, month, year, advisor, team, etc.
Each case no. can have one or multiple advisors assigned to it. Each case can have multiple rows of data as Revenue for each case can be +ve (payment) or -ve (refund) for each advisor.
The issue I am facing is that when I put this measure in a KPI card, sliced for 2022 YTD, it is showing 238 cases (incorrect), however if I put the measure in a table along with Case No. then it shows 230 (Correct).
Any guidance would be greatly appreciated. Thanks in advance!
arnabmit ,
Not exactly sure why this is happening, but it may have something to do with your SUM filter condition.
You can comment this one condition out of the Measure ("//" ) and see how that changes the results in your two. Visuals.
Next, I would attack it this way:
Select your Card Visual and change it to a table. You should get a value of 238 as a one column, one row table.
Then start adding your columns to this table. Hoping you then might be able to see the differences between your 238 and your 230.
Hope these thoughts lead you in the right direction.
Regards,
1 Reply
arnabmit ,
Not exactly sure why this is happening, but it may have something to do with your SUM filter condition.
You can comment this one condition out of the Measure ("//" ) and see how that changes the results in your two. Visuals.
Next, I would attack it this way:
Select your Card Visual and change it to a table. You should get a value of 238 as a one column, one row table.
Then start adding your columns to this table. Hoping you then might be able to see the differences between your 238 and your 230.
Hope these thoughts lead you in the right direction.
Regards,