topic Calculate Measure base on filter selection in Power Query

Calculate Measure base on filter selection

Anonymous — Fri, 10 Jul 2020 08:13:05 GMT

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.

Re: Calculate Measure base on filter selection

lbendlin — Fri, 10 Jul 2020 11:02:47 GMT

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.

Re: Calculate Measure base on filter selection

v-alq-msft — Mon, 13 Jul 2020 01:59:46 GMT

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.

Re: Calculate Measure base on filter selection

hbins181 — Thu, 21 Sep 2023 18:59:49 GMT

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