Calculate Measure base on filter selection

[Anonymous]

Hi,

I've four filters based on that i need to create Measere.

Example:

Consider P1,P2,P3& P4 as Filters

P1	P2	P3	P4
RED	RED	RED	RED
WHITE	WHITE	WHITE	WHITE
BLUE	BLUE	BLUE	BLUE
YELLOW	YELLOW	YELLOW	YELLOW
NA	NA	NA	NA

Note :

1.User select any priorty

     P1=RED

     P2=WHITE OR YELLOW OR BLUE OR NA

similarly P3 and P4.

Output measure:

Colours	Counts
Red	8
white	8
Yellow	1
Blue	1

Advance Thanks,

Saikiran.

[hbins181]

The way I did it was a bit different, I needed a Selection Slicer to Determine which Lines to draw in a LineGraph. I drew one line in the Main Y-Axis and the second line in the Secondary Y-Axis. If there is no selection, blank graph. If "All" is selected it shows both.

1. I first created a custom column with the values I am filtering by (In my case it was Density and Sulphur Values)
2. (OPTIONAL) Add an All value in custom column if you desire to show all option
3.  Create a first measure - My Example:

SulphurMeasure =

VAR Density_Values = SUM(Hardisty[Absolute Density (kg/m3)]) //Line Value1

VAR Sulphur_Values = SUM(Hardisty[Sulphur (wt%)])                  //Line Value2

VAR SELECTION =

SWITCH

(

    SELECTEDVALUE(Toggle[Toggle]), //Finds the selected value. Toggle[Toggle] is what I am using to filter

    BLANK(), BLANK(),                         //If blank print blank (OPTIONAL)

    "Sulphur (wt%)", Sulphur_Values,  //If Sulphur, draw sulphur line

    "ALL", Sulphur_Values                   // If All, draw sulphur line (OPTIONAL)

    )

RETURN  SELECTION

4. Create a second measure - Basically same but opposite - My Example:

DensityMeasure =

VAR Density_Values = SUM(Hardisty[Absolute Density (kg/m3)]) //Line Data1

VAR Sulphur_Values = SUM(Hardisty[Sulphur (wt%)]) //Line Data2

VAR SELECTION =

SWITCH

(

    SELECTEDVALUE(Toggle[Toggle]), //The currently selected value in filter

    BLANK(), BLANK(),                        //If nothing selected show nothing (OPTIONAL)

    "Absolute Density (kg/m3)", Density_Values,    //If Density, draw density

    "ALL", Density_Values                                        //If All, draw density (OPTIONAL)

    )

RETURN  SELECTION

5. After you've created the measures, Apply them to the graph, (y-axis & secondary y-axis)
6. Create a Slicer using the custom column from Step 1

Hope this helps!
Hasan B

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Filters:

Calculated tables:

P1 = DISTINCT('Filters'[P1])
P2 = DISTINCT('Filters'[P2])
P3 = DISTINCT('Filters'[P3])
P4 = DISTINCT('Filters'[P4])

Table:

There is no relationship between tables. You may create a measure as below.

Count = 
COUNTROWS(
    FILTER(
        DISTINCT(P1[P1]),
        [P1]=SELECTEDVALUE('Table'[Colors])
    )
)+
COUNTROWS(
    FILTER(
        DISTINCT(P2[P2]),
        [P2]=SELECTEDVALUE('Table'[Colors])
    )
)+
COUNTROWS(
    FILTER(
        DISTINCT(P3[P3]),
        [P3]=SELECTEDVALUE('Table'[Colors])
    )
)+
COUNTROWS(
    FILTER(
        DISTINCT(P4[P4]),
        [P4]=SELECTEDVALUE('Table'[Colors])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

you will need more than just one measure.  you will need one measure per color value.  Use the FILTERS() function to collect the actual values for each filter.