Creating Measure based on the value of a type

[MAAbdullah47]

Dear All ,

I have 2 table fields (columns) as the following:

1- Real Estate Type (RE) = (Vella,Apartment,Building and Land)

2-Area Size (AS) = numeric (square meter (SQM)).

I need to create a measure the I can put in the Report (page) date filtering with the following conditions:

If RE='Vella' , Maximum area size = 1000 SQM

RE='Apartment' , Maximum Area Size = 130 SQM ,................etc

How can I create this measure and filtering it in any level (Report or page or visual)?

[v-shex-msft]

Hi @MAAbdullah47,

I'd like to suggest you create a selector table with "Vella" and "Apartment". Then use it as the source of slicer.
After these steps, write a measure to dynamic change displayed value based on slicer.

Below are some sample formulas.

Table formula:

Selector = DATATABLE("Type",STRING,{{"Vella"},{"Apartment"}})

Measure:

Dynamic Display = 
var selectItem=SELECTEDVALUE(Selector[Type])
return
SWITCH(selectItem,"Vella",MAX('Records'[Vella]),"Apartment",MAX('Records'[Apartment]))

Result:

 

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Thank you v-shex-msft for the answer

I might say the question wrong (my mistake) the Idea is each type has a dynamic value as the following data:

ID     Location      Category                        Size

1         North            Villa                          6000    

2         South           Villa                            850

3         East              Apartment                 1500

4         East              Apartment                 120

My Target is avoiding(withdrawing) the outliers for each category (cuz there is mistakes in entering the data), for example, I can make the maximum distance (2000 SQM), but this will make problem to the (apartment) category. Each category has its own type of outlier number range and I need to show statistics for both types on the same page as shown below:

Note The data in the picture written in Arabic but I translated it in the next lines.

In (1) I need to set some filter using IF statement to maximize the size field e.g.:

If Category = 'Villa' , Max Size = 1000 else size

If Category = 'Apartment', Max Size = 130 else size

For (2) the results show an unreasonable average size for apartments (in all areas of the country the maximum area size of apartments is (130-150).  

I hope the clarification above is clear and if you need further details let me know.

Thank You  

m,.m,m

 

[MAAbdullah47]

To make it more clear, please look at the data below:

ID     Location      Category                        Size

1         North            Villa                          6000    

2         South           Villa                            850

3         East              Apartment                 1500

4         East              Apartment                 120

(1,3) should be filtered from the original data.

Thank You

[MAAbdullah47]

In (1) I need to set some filter using IF statement to maximize the size field e.g.:

If Category = 'Villa', The Max Size should not exceed 1000.

If Category = 'Apartment', The Max Size should not exceed 130.

[v-shex-msft]

HI @MAAbdullah47,

So, after you select the type, you want to find out the relative max value from table, right?

Sample:

Dynamic Display =
VAR selectItem =
    SELECTEDVALUE ( Selector[Type] )
RETURN
    SWITCH (
        selectItem,
        "Vella", MAXX ( FILTER ( ALLSELECTED ( 'Records' ), [Category] = Vella ), [Size] ),
        "Apartment", MAXX ( FILTER ( ALLSELECTED ( 'Records' ), [Category] = Apartment ), [Size] )
    )

Then add a measure to check current value and add a tag and drag this tag measure to filter to apply the filter effect.

Regards,

Xiaoxin Sheng

[MAAbdullah47]

Now everything is clear but still one point, you said: "Then add a measure to check current value and add a tag and drag this tag measure to filter to apply the filter effect." , how this work? ( I didn't get your point)

[v-shex-msft]

Hi @MAAbdullah47,

According to above formula, we can get the maximum value of specific category based on slicer, right?
Since the slicer works on new table who not contains the relationship to original tables. If you need to filter not matched records, you can add a measure to compare the above value and current row value.(for e.g. return 1 if current value larger or equal to maximum value, other wise 0)
Then drag this tag measure to filter to apply the filter effect.

Regards,

Xiaoxin Sheng