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   P1 P2 P3 P4 RED RED RED RED WHITE  WHITE  WHITE  WHITE  B...
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.

     

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