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

[ShaiG]

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 ?

[StrategicSavvy]

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:

https://drive.google.com/file/d/19iCfGyKotdnpFqFamX0ok5wEIy_mo8-Y/view?usp=sharing 

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 

[ShaiG]

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 

[gmsamborn · Accepted solution]

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

[ShaiG]

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 

[gmsamborn]

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?  

[ShaiG]

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 

[ShaiG]

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 

[ShaiG]

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]

[lbendlin]

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

[lbendlin]

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

[ShaiG]

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 ?