Capture range of cases based on column count

[Anonymous]

I currently have two columns of data:

Identifier Count Latest Week & Number of cases associate with each identifier:

I'm trying to create a measure to capture the range of cases according to each location e.g. below 1-3 OR 2-21

Location	Identifier Count Latest Week	Number of Cases	Range
Comm. Hosp.	1	1	1-3
Comm. Hosp.	1	3	1-3
Hospital	2	10	2-21
Hospital	1	13	2-21
Hospital	4	2	2-21
Hospital	3	21	2-21
Hospital	2	7	2-21

I've tried :

Range = FORMAT(CALCULATE(MIN('Sheet1'[Column Count Latest Week])) ,"#") & 
" - " & FORMAT( CALCULATE(MAX('Sheet1'[Column Count Latest Week])), "#") 

but no luck

can anyone help?

Sample

https://www.dropbox.com/s/3eba6f6fsz7hfem/Range%20Sample.pbix?dl=0

[Ashish_Mathur]

Hi,

Do you want a calculated column formula solution or a measure solution?

[Anonymous]

I think a measure would work best

[Ashish_Mathur]

Hi,

See if the Dynamic segmentation technique helps - Dynamic segmentation – DAX Patterns

[mahoneypat]

I looked at your pbix file.  Before a solution for Range can be suggested, here are a couple suggestions/questions.

1. For your Area Grouping column, the use of a Groups column would be easier and easier to update later (vs. the use of SWITCH(TRUE() you've used; it works but just showing an easier way).  Please see this link.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Docs

2.  I believe everything you want to do can be accomplished with measures instead of columns.  If you plan to use the column in a visual legend, axis, etc., a column is needed though.

3.  Your Total Count Latest Week measure is overly complex.  Please explain the calculation you need and a simpler solution can be suggested.

Regards,

Pat

[Anonymous]

Agreed the grouping solution could be better edited via Power BI groupings rather than DAX.

Measures seem to be the best way to go with matrix tables.

All I'm trying to do is get the number of [Identifier Count] & [No. of Cases] & [Range Min-Max] for the most current week number e.g. 47