Calculated Values in a Chart

[SoupyNoodles14]

Below is a table that I built in SQL using a cross join for each customer visit-priorService, where we provide an "as of" list of prior services done for each customer visit to a store. At visit 1, customer A has only one prior high-level service done on 1/1/2006. At visit 2, that same customer has two prior services done, S1 high and S2 medium. Finally, at visit 3, that same customer has four prior services done, S1, S2, S3, and S4. 

It is possible for a customer to have no prior services done, as indicated by customer D. This table is only part of a star-schema where my dashboard is centered on visits, so an entry in this table NEEDS to be recorded for each visit in the table below to identify that that visit for customer D is associated with 0 prior services. 

Each prior service has a severity and date (as well as other fields not listed in the table I have already created below). 

"Priors" Table:

Customer	VisitID	Visit Date	PriorServiceID	PriorServiceDate	PriorServiceSeverity
A	V1	1/1/21	S1	1/1/06	High
A	V2	6/1/21	S1	1/1/06	High
A	V2	6/1/21	S2	3/1/21	Medium
A	V3	8/1/21	S1	1/1/06	High
A	V3	8/1/21	S2	3/1/21	Medium
A	V3	8/1/21	S3	7/15/21	Low
A	V3	8/1/21	S4	7/17/21	Medium
B	V5	7/1/22	S5	1/1/01	High
B	V5	7/1/22	S6	1/10/01	Medium
C	V6	1/1/21	S7	1/1/98	Medium
C	V6	1/1/21	S8	6/1/05	Low
C	V7	9/1/21	S7	1/1/98	Medium
C	V7	9/1/21	S8	6/1/05	Low
C	V7	9/1/21	S9	1/1/21	High
D	V8	10/1/21	NULL	NULL	NULL

The dashboard I've created has slicers on visit date, prior service date, and prior service severity. 

I want to chart with the # of FILTERABLE prior services on the x-axis, and the number of visits on the y-axis. 

To achieve this, I initially built the following summary table separately in my data model.

Priors Counting Table = SUMMARIZE( 'Priors', Priors[VisitID], "PriorVisitCnt", DISTINCTCOUNTNOBLANK( 'Priors'[PriorServiceID] ) + 0 )

I linked this to my main visit-level table using the VisitID and got the chart I wanted, but the chart becomes meaningless when I filter on the service date or service type. Without any restrictions, there are upto 190 prior services done for a customer, but we need to see only the prior services done in 2017 when I filter on that service year for instance. It still shows 190 when I select 2017 on the slicer because there is a customer who had a grand total of 190 visits between 2000 (the oldest service recorded) and 2024 whose total amount of services keep showing. Based on my review of similar posts on this forum, it looks like measures can't be thrown into charts so easily but there COULD BE a workaround through calculated columns? I'm struggling a lot on this to even get the count of prior services per EACH visit on a measure to begin with. I already report on the min, max, median, and mean number of prior services done as of each visit for a customer, and it's very easy using a measure such as below. So I'm HOPEFUL something like this can be done at the chart-level?

Median = 

VAR Summary = SUMMARIZE('Priors', Priors[VisitID], "PriorVisitCnt", DISTINCTCOUNTNOBLANK( [PriorServiceID] ) + 0 )
RETURN MEDIANX ( Summary, [PowerBICnt] )

[Anonymous]

Hi @SoupyNoodles14 ,

Did you try the solution I provided, looking forward to your feedback.

Best regards,

Adamk Kong

[SoupyNoodles14 · Accepted solution]

Unfortunately it did not, but thank you for trying. FYI, when I initially posted this, it was flagged as spam incorrectly and so I re-posted the question here: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Dynamic-Measure-of-Distinct-C.... The solution is working there. Someone at Microsoft un-flagged this original post. 

[Anonymous]

Hi @SoupyNoodles14 ,

Depend on your description,  maybe you can try formula like below to create measures:

Prior Service Count Per Visit = 
CALCULATE (
    DISTINCTCOUNT ( 'Priors'[PriorServiceID] ),
    ALLEXCEPT ( 'Priors', 'Priors'[VisitID] )
)

Prior Services Count = 
CALCULATE (
    DISTINCTCOUNT ( 'Priors'[PriorServiceID] ),
    FILTER(Priors,[Measure] = 0)
)

Visit Count = CALCULATE ( DISTINCTCOUNT ( 'Priors'[VisitID] ) )

Measure = IF(MAX(Priors[PriorServiceID]) = BLANK(),1,0)

Best Regards,
Adamk Kong

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

[ahadkarimi]

Hi @SoupyNoodles14, try two measures below, and if you encounter any issues, let me know.

Prior Service Count = 
VAR CurrentVisitID = SELECTEDVALUE('Priors'[VisitID])
RETURN
CALCULATE(
    COUNTROWS('Priors'),
    FILTER(
        'Priors',
        'Priors'[VisitID] = CurrentVisitID && 
        NOT(ISBLANK('Priors'[PriorServiceID])) -- Exclude blanks (NULLs)
    )
)

Visit Count = 
CALCULATE(
    COUNTROWS('Priors'),
    ALLEXCEPT('Priors', 'Priors'[VisitID]) -- Ensures grouping by VisitID
)

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!

[SoupyNoodles14]

Unfortunately @ahadkarimi what's happening with this is that PowerBI is not showing anything for the first measure you've suggested "Prior Service Count" and the second measure "Visit Count" is not returning a meaningful value. "Visit Count" is returning a number that is neither the total number of visits nor prior services and "Prior Service Count" is listing blank.