Distinct count of column depending on order of values in another column

[onedayover]

Hi Community

I'm struggling with how to do this and hope someone can help.

I have a table and need a measure that counts the distinct centres based on their status order in a list.

Each centre can have 3 possible status - "available", "created", "none".

I need to count each centre once based on their records and in this order.

So if a centre has both "available", "created" status rows, it is only counted in the "available" measure.

If a centre only has "created" status rows, it is only counted in the "created" measure.

This is what my table currently looks like...

centre	centre_name	status	id
C128	Centre-128	created	A12325060
C128	Centre-128	available	A12325708
C128	Centre-128	created	A12325640
C258	Centre-258	available	A12324992
C258	Centre-258	none	A12325512
C258	Centre-258	created	A12324986
C258	Centre-258	created	A12324929
C621	Centre-621	none	none
C647	Centre-647	created	A12351128
C647	Centre-647	none	A12351127
C654	Centre-654	created	A12324845
C654	Centre-654	created	A12351912

And this is the result I need...

status	count
available	2
created	2
none	1

Thanks

onedayover

[onedayover]

Thank you both, this is nearly what I need.

Using the onedayover.pbix example file attached, when I filter each status separately, the correct counts and total is shown and the same for when I filter just "available" and "created" status. However, I'm hoping to be able to use the combined total for "created" and "none" count (3) in a card visual on it's own. When I try this, it shows a total count of 5. Is there a way I can change the DAX so that I am able to use these counts (2,2,1) either individually or combined? Thanks

[Jihwan_Kim]

Hi,

I am not sure whether I understood your question correctly, but please check the below picture and the attached pbix file.

Status count measure: =
VAR _availablelist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "available" ), Data[centre] )
VAR _createdlist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "created" ), Data[centre] )
VAR _nonelist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "none" ), Data[centre] )
VAR _availableandcreatedlist =
    INTERSECT ( _availablelist, _createdlist )
VAR _onlycreatedlist =
    EXCEPT ( _createdlist, _availableandcreatedlist )
VAR _onlynonelist =
    EXCEPT ( EXCEPT ( _nonelist, _availablelist ), _createdlist )
RETURN
    IF (
        HASONEVALUE ( Data[status] ),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Data[status] ) = "created", COUNTROWS ( _onlycreatedlist ),
            SELECTEDVALUE ( Data[status] ) = "none", COUNTROWS ( _onlynonelist ),
            COUNTROWS ( DISTINCT ( Data[centre] ) )
        ),
        COUNTROWS ( _onlycreatedlist ) + COUNTROWS ( _onlynonelist )
    )

[onedayover]

Hi Jihwan_Kim, my apologies for taking so long in replying. I've tried your solution and have realised I was wrong in what I needed to achieve and have not explained this well. It's not so much the measure that I need but a final list/table that looks like this...

centre	centre_name	status
C128	Centre-128	available
C258	Centre-258	available
C621	Centre-621	none
C647	Centre-647	created
C654	Centre-654	created

So basically, if a centre has a status of "available" for any row, then it is "available", if a centre has no "available" status for any course_id but has "created" row/s, then it is "created", if a school only has "none" as status, then it is "none". All centres need to appear only once in the final table. Please let me know if you need further explanation on this.

Thank you again and my apologies for not getting this right the first time.

[Ashish_Mathur]

Hi,

You have used 2 terms there which are nowhere in your sample dataset - Course_ID, School.  Anyways, based on the data that you have shared, show the expected result.

[onedayover]

Hi Ashish

Apologies for the confusion. Where I mention "school" I actually meant "centre" and "course_id" is just "id".

So my data table is like this...

centre	centre_name	status	id
C128	Centre-128	created	A12325060
C128	Centre-128	available	A12325708
C128	Centre-128	created	A12325640
C258	Centre-258	available	A12324992
C258	Centre-258	none	A12325512
C258	Centre-258	created	A12324986
C258	Centre-258	created	A12324929
C621	Centre-621	none	none
C647	Centre-647	created	A12351128
C647	Centre-647	none	A12351127
C654	Centre-654	created	A12324845
C654	Centre-654	created	A12351912

And this is what I'm trying to acheive...

centre	centre_name	status
C128	Centre-128	available
C258	Centre-258	available
C621	Centre-621	none
C647	Centre-647	created
C654	Centre-654	created

Thanks

onedayover

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = if(CALCULATE(COUNTROWS(Data),Data[status]="Available")>0,"Available",if(CALCULATE(COUNTROWS(Data),Data[status]="Created")>0,"Created","None"))

Hope this helps.

[onedayover]

It certainly does! Thank you so much for that. I just couldn't work that one out.

Your help is much appreciated 🙂

onedayover

[Ashish_Mathur]

You are welcome.

[FreemanZ]

hi @onedayover 

try to plot a table visual with status column and a measure like:

countmeasure =

VAR _a=

CALCULATE(

    DISTINCTCOUNT(TableName[centre]),

    ALL(TableName),

    TableName[status]="available"

)

VAR _c=

CALCULATE(

    DISTINCTCOUNT(TableName[centre]),

    ALL(TableName),

    TableName[status]="created",

    TableName[status]<>"available"

)

VAR _n=

CALCULATE(

    DISTINCTCOUNT(TableName[centre]),

ALL(TableName),

    TableName[status]="none",

    TableName[status]<>"available"

    TableName[status]<>"created"

)

RETURN

SWITCH(

    MAX(TableName[status]),

    "available", _a,

    "created", _c,    

    "none", _n

)

[FreemanZ]

hi @onedayover 

after reading Jihwan's code and propose the following:

Count = 
VAR _lista =
CALCULATETABLE(
    VALUES(TableName[centre]),
    TableName[status]="available"
)
VAR _listc =
CALCULATETABLE(
    VALUES(TableName[centre]),
    TableName[status]="created"
)
VAR _listn =
CALCULATETABLE(
    VALUES(TableName[centre]),
    TableName[status]="none"
)
VAR _counta = COUNTROWS(_lista)
VAR _countc = COUNTROWS(EXCEPT(_listc, _lista))
VAR _countn = COUNTROWS(EXCEPT( EXCEPT(_listn, _lista), _listc))
RETURN
SWITCH(
    MAX(TableName[status]),
    "available",_counta,
    "created",_countc,    
    "none",_countn
)

verified like:

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Status count measure: =
VAR _availablelist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "available" ), Data[centre] )
VAR _createdlist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "created" ), Data[centre] )
VAR _nonelist =
    SUMMARIZE ( FILTER ( ALL ( Data ), Data[status] = "none" ), Data[centre] )
VAR _availableandcreatedlist =
    INTERSECT ( _availablelist, _createdlist )
VAR _onlycreatedlist =
    EXCEPT ( _createdlist, _availableandcreatedlist )
VAR _onlynonelist =
    EXCEPT ( EXCEPT ( _nonelist, _availablelist ), _createdlist )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Data[status] ) = "created", COUNTROWS ( _onlycreatedlist ),
        SELECTEDVALUE ( Data[status] ) = "none", COUNTROWS ( _onlynonelist ),
        COUNTROWS ( DISTINCT ( Data[centre] ) )
    )