Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Measure Filtered by Group of slicers one for Y and the other for Y secondary

Hi

lets say i have this sample data

 ParamA Step paramC Lot value Test1 A conc 1234 5 Test1 B conc 1234 7 Test2 A Load 1234 8 Test2 B conc 1234 6 Test2 B Load 1234 3 Test1 A conc 5555 9 Test1 B conc 5555 4 Test2 A Load 5555 3 Test2 B conc 5555 6 Test2 B Load 5555 2 Test1 A conc 7777 6 Test1 B conc 7777 2 Test2 A Load 7777 5 Test2 B conc 7777 9 Test2 B Load 7777 4

i want to create a group of filters that control one measure value sum

and another group of filters that control the second measure value sum

so i can compare them together in the same chart

on the i want the LOT

And on the y i want measure 1 that is controled by the first group of slicers

and on the secodary y i want measure 2 that is controled by the second group of slicers

every group has slicers for ParamA , Step and ParamC

i can do it with two diffrent visuals but i want it for comparsment in twho lines

here is an example of using two visuals for demonstration

any one have an idea how to solve with dax ?

1 ACCEPTED SOLUTION
Super User

Hi @ShaiG

Would duplicating your table be an option?

In Power Query I made a copy (FactTable and FactTable2).  In addition, I created a Lot dimension table.  (DimLot)

Add 1:many (single) relationships between DimLot and the fact tables.

It makes the measures very simple:

``````Val1 = SUM( 'FactTable'[value] )

Val2 = SUM( 'FactTable2'[value] )``````

It might be an option.

Measure Filtered by Group of slicers one for Y and the other for Y secondary.pbix

11 REPLIES 11
Resolver II

hi,

I think it might be worth to create set of disconnected tables. in your case it will be 6 tables for 2 groups of parameters. For that you can use either VALUES or DISTINT (if you want to drop blanks)  function to create calculated tables which will serve as calculate modifiers.

Link to pbix:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: www.linkedin.com/in/lukasz-kozdron

Frequent Visitor

hey , thank you for your post

unfortnatly  im looking for no duplicate tables solutions , there was already one solution that had 2 copy tables , but still im lookling for a no duplicate tables soluition .

thank you eitherway

Super User

Hi @ShaiG

Would duplicating your table be an option?

In Power Query I made a copy (FactTable and FactTable2).  In addition, I created a Lot dimension table.  (DimLot)

Add 1:many (single) relationships between DimLot and the fact tables.

It makes the measures very simple:

``````Val1 = SUM( 'FactTable'[value] )

Val2 = SUM( 'FactTable2'[value] )``````

It might be an option.

Measure Filtered by Group of slicers one for Y and the other for Y secondary.pbix

Frequent Visitor

this is actually one possible option i know can work

but im trying to avoid to duplicate the all table cause its a big one  and cause truble on filtering group A effecting Group B ( in this sample i used a small sample )

if you have a better option it can help

Super User

I'm not sure what you mean by "cause truble on filtering group A effecting Group B".  With separate tables that wouldn't be an issue.

How many rows in your table?

Frequent Visitor

60K , but need to use alot of slicers

still my client is using this kind of solution ( and its slow working )
i was hoping for a better one

maybe using AllExcept or something but it doesnt work , and i didnt understand why i cant use

two measures with diffrent slicers and show them together

Frequent Visitor

try also simple AllExcept

ValueX = CALCULATE(Sum('Sample'[value]) ,ALLExcept('Sample','Sample'[Lot],'Sample'[ParamA] ,'Sample'[Step] , 'Sample'[paramC]))
same for ValueY with selected columns
Frequent Visitor

in performance Analyzer it gives me this code that it doesnt show the ALExcept measure , but just the name of it
and its filtered by all sliceres so it gives me empy reasult

so i dont understand why it is

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({1234,
7777,
5555}, 'Sample'[Lot])

VAR __DS0FilterTable2 =
TREATAS({"B"}, 'Sample'[Step])

VAR __DS0FilterTable3 =
TREATAS({"Test1"}, 'Sample'[ParamA])

VAR __DS0FilterTable4 =
TREATAS({"conc"}, 'Sample'[paramC])

VAR __DS0FilterTable5 =
TREATAS({"Test2"}, 'Sample'[YparamA])

VAR __DS0FilterTable6 =
TREATAS({"A"}, 'Sample'[Ystep])

VAR __DS0FilterTable7 =
TREATAS({"Load"}, 'Sample'[YparamC])

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Sample'[Lot],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__DS0FilterTable7,
"ValueX", 'Sample'[ValueX],
"ValueY", 'Sample'[ValueY]
)

VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, [ValueX], 0, 'Sample'[Lot], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[ValueX] DESC, 'Sample'[Lot]

Super User

sorry, still not clear to me. I hope someone else can help you further.

Super User

not clear to me what you are trying to achieve.  What are the measure definitions?

Frequent Visitor

Hey ,

i'm trying to achive a comparesment between two measures  in the same chart , with diffrent set of filters
the first measure is the sum of values with group A filters , that shows for every lot seleceted the value

the second measure is the sum of values with Group B filters , that show for every lot selected the value ( in a secondary line )

i started to try an idea i had to duplicate ParamA , Step , paramC and value to extra columns

and then create measures

ValueX = CALCULATE(Sum('Sample'[value]) ,ALLSELECTED('Sample'[Lot],'Sample'[ParamA] ,'Sample'[Step] , 'Sample'[paramC]))
ValueY = CALCULATE(Sum('Sample'[Yvalue]) ,ALLCROSSFILTERED('Sample'),ALLSELECTED('Sample'[YparamA] ,'Sample'[Ystep] , 'Sample'[YparamC],'Sample'[Lot]))

but it doesnt work for some reason

any other ideas someone might have ?

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors