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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ShaiG
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 

ParamAStepparamCLotvalue
Test1Aconc12345
Test1Bconc12347
Test2ALoad12348
Test2Bconc12346
Test2BLoad12343
Test1Aconc55559
Test1Bconc55554
Test2ALoad55553
Test2Bconc55556
Test2BLoad55552
Test1Aconc77776
Test1Bconc77772
Test2ALoad77775
Test2Bconc77779
Test2BLoad77774

 

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 ?

Screenshot 2024-02-22 at 17.18.24.png

 

1 ACCEPTED SOLUTION
gmsamborn
Super User
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

 

View solution in original post

11 REPLIES 11
StrategicSavvy
Resolver II
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. 

 

 

 

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

 

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 

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
Super User
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

 

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 

 

 

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?  

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
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 
ShaiG
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]

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

lbendlin
Super User
Super User

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

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors