Pie chart multiple value dynamically calculated

[Anonymous]

Hello,

I quiet new in DAX and I need your help with this use case.

Here is sample data

STATUS_ID	FILE_ID	STATUS_DATE	STATUS
1	1	2020-05-08	NEW
2	2	2020-05-10	NEW
3	1	2020-05-10	UPDATED
4	2	2020-05-11	UPDATED
5	1	2020-05-12	CLOSED
6	3	2020-05-12	NEW

Based on this data, I want to display a Pie Chart with the number of distinct FILE_ID by STATUS. 

But the tricky part ( for my point of view) is :

1. If a FILE_ID has multiple status it must be count in a status name "MULTIPLE STATUS"
2. I want to add a slicer to filter by STATUS_DATE

The exepecting result based on my sample date is :

•  if my date slicer is between 2020-05-08 and 2020-05-12 : 2 MULTIPLE STATUS and 1 NEW
• if my date slicer is between 2020-05-11 and 2020-05-12 : 1 UPDATED, 1 CLOSED and 1 NEW

I don't know how to manage that... 

Many thanks for your help ! 

[Anonymous]

Thanks @Jihwan_Kim  for this quick answer. 

I think the VAR currentstatus is useless since it's not used in the dax formula.

But otherwise it works perfectly ! 

Thanks a lot ! 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I suggest having a STATUS Table like below.

File count by Status =

VAR currentstatus =

MAX ( 'Status'[STATUS] )

VAR multiplestatustable =

SUMMARIZE (

FILTER (

SUMMARIZE (

ALLSELECTED ( Data ),

Data[FILE_ID],

"@statuscount", COUNTROWS ( VALUES ( Data[STATUS] ) )

),

[@statuscount] > 1

),

Data[FILE_ID]

)

VAR notmultiplestatustable =

EXCEPT ( VALUES ( Data[FILE_ID] ), multiplestatustable )

RETURN

SWITCH (

SELECTEDVALUE ( 'Status'[Index] ),

4, COUNTROWS ( multiplestatustable ),

COUNTROWS ( notmultiplestatustable )

)

https://www.dropbox.com/s/2zhy4zr7rvgqflj/bibus.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM