Need to count a measure. Need help

[Bokazoit]

I have this measure:

ColdSpotClm = IF([Delivered hours] > 18 && [UsedPercent]<0,6;"Big";IF([Delivered hours] >= 6 && [Delivered hours]<=18 && [UsedPercent]<0,5;"Medium";IF[Delivered hours] >= 3 && [Delivered hours]<= 5 && [UsedPercent]<0,4;"Small";"")))

I need another measure that I can use in map charts - specifically as size of the bubble, that counts the number of large, medium and small business' that the above measure finds.

[v-yuezhe-msft]

@Bokazoit,

Please create the following new table.

Table = SUMMARIZE(Table;Table1[yourcategorycolumn];"Hour";[Delivered Hours];"type";[ColdSpotClm]) 

Then create the following measure in your new table.

Measure  = COUNTA('Table'[type])

If the above DAX don't return your expected result, please share sample data of your table and post the DAX you use to calculate Delivered hours and UsedPercent.

Regards,
Lydia

[Bokazoit]

The picture below shows the dashboard with the data in a table and a map as well as slicers. I have this measure ColdSpotClm:

ColdSpotClm = IF(SUM(Test[Udbudte]) > 18 && DIVIDE(SUM(Test[Forbrugt]);SUM(Test[Udbudte]))<0,6;1;IF(SUM(Test[Udbudte]) >= 6 && SUM(Test[Udbudte])<=18 && DIVIDE(SUM(Test[Forbrugt]);SUM(Test[Udbudte]))<0,5;1;IF(SUM(Test[Udbudte]) >= 3 && SUM(Test[Udbudte])<= 5 && DIVIDE(SUM(Test[Forbrugt]);SUM(Test[Udbudte]))<0,4;1;0)))

The measure ColdSpot has the value 1 if ok and 0 if not. In my real measure it is named "Large", "Medium" and "Small" or Empty cell. But for now 1 or 0 is ok.

As can be seen from the screenshoot above there is 12 healthcenters with a ColdSpot and the rest is out of scoop. And now comes my problem. I need to show these 12 hotspots in the map as well.

I have tried different measures but with no luck so far (in the picture above) my measure is 227 and not 12 as it is suppose to be.

The measure ColdSpotClm can not be used in the map for bubble size so I tried this measure:

Forbrugt for Region Hovedstaden = 
VAR minDate =
    MIN ( Test[Periode] )
VAR maxDate =
    MAX ( Test[Periode] )
VAR ColdSpot = 1

RETURN
    CALCULATE(
        COUNT(Test[healthcentername]);
        FILTER (Test;
            Test[Periode] >= minDate
                && Test[Periode] <= maxDate)
        
)

But that gives me 227.

I need to use a measure since I later on has to add a parameter as part of the slicers, so creating a column is not a way since the parameter won't work with parameters

[v-yuezhe-msft]

@Bokazoit,

Please share sample data of your Test table here so that we can copy and paste to repro.

Regards,
Lydia

[Bokazoit]

Hi  @v-yuezhe-msft

Can You see the Sample data in the link below?

Sample data

[v-yuezhe-msft]

@Bokazoit,

I am unable to access the above data since it requires permission.

Regards,
Lydia

[Bokazoit]

Can You try again @v-yuezhe-msft . I have tested with a collegue now 🙂

Sample data

[Bokazoit]

Up

[Bokazoit]

@v-yuezhe-msft can You see the sample data? 

[v-yuezhe-msft]

@Bokazoit,

Thanks for sharing the sample data, but I can't find the Forbrugt column in the sample table.


Regards,
Lydia

[Bokazoit]

Forbrugt =  ProductionUnits

[Bokazoit]

Hello @v-yuezhe-msft anything new?

[v-yuezhe-msft · Accepted solution]

@Bokazoit，

I am afraid that it is not possible to achieve your requirement, in other words, it is not possible to show 12 in the map visual. ColdSpotClm is a measure, it will be re-calculated according to the fields you drag to the Map.

Regards,
Lydia