Show Category that does not appear

[villa1980]

Hi all,

 I have a list of Bays within a table Bay1,Bay2,Bay3,Bay4,Bay5,Bay6.

 Sometimes during the week not all these Bays are used and what I would like to do is to count the number of bays not used and what the name is? I am not sure how to do this? Do I go through the DAX route or do I create a separate table with these values in and join it to the main table and use DAX from that?

 Any thoughts and Ideas would be much appreciated.

Thanks

Alex

[Selva-Salimi]

Hi @villa1980 

you can do as follows:

measure lost :=

     var _List_of_Bays = values (bays_table [bays])

     var _week_list = values (week_table [bays])

     var _not_include= except (_list_of_bayes , _week_list)

     return  concatenatex (_not_include , ',')

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[villa1980]

Hi Selva-Salimi,

 Thank-you for the reply, I forgot to mention that I am using a Date column to determine future availability.
Unfortunately it returns an error that states Function 'EXCEPT' does not support joining a column of type Text with a column of type Date

[Selva-Salimi]

it was not clear for me. Did you mean that you use date column to find future availability of bays?? if so, I think you need to use the following:

measure lost :=

     var _List_of_Bays = values (bays_table [bays])

     var _week_list = summarize (filter (week_table , date> today()) , week_table [bays] )

     var _not_include= except (_list_of_bayes , _week_list)

     return  concatenatex (_not_include , ',')

if not, please share more details. I think in the other table you should have a list of bays that you want to compare with the full available lists.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[villa1980]

My apologies, was trying to word it best I could and forget some key things 🙂

Now is erroring due to the one table having multiple values...

This will make it easier if I List tables and columns 🙂 
EXTR_STG_FOCUS APPOINTMENTS, contains STARTDATE,BAYID,CentreNo
EXTR_STG_FOCUS BAYS contains BAYID,BAYDESC

EXTR_STG_FOCUS BAYS is joined to EXTR_STG_FOCUS APPOINTMENTS on BAYID

[Selva-Salimi]

will you select the startdate on slicer? or will you show dates on visuals?!

[Selva-Salimi · Accepted solution]

if you use your date in slicer or on visuals, update the measure as follows:

measure lost :=

var _List_of_Bays = summarize ('EXTR_STG_FOCUS BAYS' , 'EXTR_STG_FOCUS BAYS' [BAYID]

var _week_list = summarize ('EXTR_STG_FOCUS APPOINTMENTS', 'EXTR_STG_FOCUS APPOINTMENTS' [BAYID] )

var _not_include= except (_list_of_bayes , _week_list)

return  concatenatex (_not_include , 'EXTR_STG_FOCUS BAYS' [BAYID] , ", " )

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[villa1980]

Thank-you for your help with this, I have found that I am looking at this too complicated, so looking at it another way. But this formula will help with other Power Bi reports I am building at the moment.

Thank-you again.

Alex

[villa1980]

Hi,

That works to bring non included Bay Id, thank-you.
If I want to show this for Centre No, would an extra line need to be added to the formula so  this will only be linked to the Centre No.

Thanks