topic Re: Measure Filtered by Group of slicers one for Y and the other for Y secondary in DAX Commands and Tips

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

ShaiG — Thu, 22 Feb 2024 15:21:24 GMT

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 ?

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

lbendlin — Fri, 23 Feb 2024 00:58:42 GMT

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

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

ShaiG — Fri, 23 Feb 2024 13:44:56 GMT

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 ?

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

ShaiG — Fri, 23 Feb 2024 13:46:44 GMT

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

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

ShaiG — Fri, 23 Feb 2024 14:04:41 GMT

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]

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

lbendlin — Sat, 24 Feb 2024 19:27:20 GMT

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

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

gmsamborn — Sat, 24 Feb 2024 20:45:31 GMT

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

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

ShaiG — Sat, 24 Feb 2024 20:56:53 GMT

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

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

gmsamborn — Sat, 24 Feb 2024 21:00:50 GMT

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?

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

ShaiG — Sat, 24 Feb 2024 21:11:49 GMT

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

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

StrategicSavvy — Sat, 24 Feb 2024 23:39:28 GMT

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

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

ShaiG — Sun, 25 Feb 2024 09:53:42 GMT

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