Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate Measure base on filter selection

Hi,

 

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

 

Example:

Consider P1,P2,P3& P4 as Filters

 

P1P2P3P4
REDREDREDRED
WHITE WHITE WHITE WHITE 
BLUEBLUEBLUEBLUE
YELLOWYELLOWYELLOWYELLOW
NANANANA

 

Note :

1.User select any priorty

     P1=RED

     P2=WHITE OR YELLOW OR BLUE OR NA

similarly P3 and P4.

Output measure:

ColoursCounts
Red8
white8
Yellow 1
Blue1

 

Advance Thanks,

Saikiran.

 

Data Protection
Need Help
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.

     

  • hbins181's avatar
    hbins181
    New Member
    2 years ago

    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