Measures as Filters, Measures as Visual Legend, Measures as Visual Filter

[shaunwilks]

Ok Ill be as brief and as detailed as possible.

In my project I needed to use a mesaure to calculate a Classification of each row. The classification calculation is very complex.

I had to use a measure because there is a What IF Parameter on the report that impacts the content of classification for each row.

So user selects a value from the slider and the classifcation measure value of each row changes value depending upon the value in the slider bar. Ie the slider bar contents are used in the classification calculation.

Table A

Index,Location,Item, Qty, Date, Value, Classification (Measure)

1,ZZ,A,200,01/01/2019,Level 1

2,ZZ,B,456,09/01/2019,Level 2

3,WW,C,309,11/01/2019,Level 4
4,ZZ,D,343,07/01/2019,Level 4

5,WW,E,230,01/01/2019,Level 4

Question 1.

How in this instance do I count the Number of records where the classifcation = "Level 1"

If it was a calculated column it would be easy.

CALCULATE(COUNT('TableA'[Index]),'TableA'[Classification]="Level 1")+0

But as its a measure a 

CALCULATE(COUNT('TableA'[Index]),[Classification]="Level 1")+0

will return an error related to the fact that a CALCULATE has been used ina true/flase expression.

One way I thought I would be able to do it is to add a Count without the filter to a Card.

Then place the classification in the Visual filter of the card and set it to "Level 1".

PBI allows me to add the measure into the Visual level filter, but then prevents me from selecting a value to apply the filter.

Its locked.

Question 2.

I have a Visual where I want to show the classification totals by Location.

The visual (100% stacked bar chart.) I have working well if the field was a calculated column,

Axis = Location

Legend = Classification

Value = Count Measure for Items

But when I had to change the Classification to a measure, The Legend can no longer be the Classfication.

Id really appreciate some feedback on this. Its driving me crazy as I had it all working well until the point where I had to introduce the slider bar imapcting the classification calculation and change the field from a calculated comlumn to measure.

[shaunwilks · Accepted solution]

Both issues were resolved using individual measures placed in the home table of the fact table.

SUMX was then used to sum the measure values.

For the replacement to the 100% stacked chart individual measures calculated the % and then they were added to a stacked chart to give a similar experience

[NewOzzie]

Can you elaborate on how you solved this? Maybe some code samples? 

Thanks

[PattemManohar]

@shaunwilks Could you please post your expected output as well to make it more clear. Also- what is the value field you are using as slicer ? 

[shaunwilks]

Whipped up a sample solution

https://www.dropbox.com/s/rev4clf282ii9a3/BI%20Community%20Solution1.pbix?dl=0

1.  The counts under the slicers listing each of the levels should be a count of the number of records if that level type.
EDIT: When I was doing the sample solution I resolved this myself so you can ignore this request.

2. The 100% stacked bar chart should have the "Level _ms" measure as the Legend - but its not allowed because its a measure.

That field cant be a changed to a calculated column either as its value is set on the fly dependant on the slider value.